Riešenie LuckyTemplates medzi dátumami pracovnej doby

V dnešnom blogu predvediem prístup k výzve Start Stop Challenge Access Analytic , kde sa pomocou LuckyTemplates vypočítajú celkové pracovné hodiny medzi dátumami. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu .

Úlohou je vypočítať celkový počet hodín zamestnanca v každom mesiaci a dátume. Uviedol som dátumy začiatku a konca, ktoré môžu presahovať mesiace a zohľadňujú stav pri riešení možných preklepov. 

Obsah

• Súbor údajov pre LuckyTemplates medzi dátumami pracovnej doby
• Filtrovanie riadkov
• Pridanie vlastného stĺpca
• LuckyTemplates Pracovná doba medzi dátumami: Premenné
• LuckyTemplates Pracovná doba medzi dátumami: Projekcia
• LuckyTemplates Pracovná doba medzi dátumami: Súhrnné hodnoty
• Záver

Súbor údajov pre LuckyTemplates medzi dátumami pracovnej doby

Poďme do editora Power Query. 

Tu sú naše údaje pre túto výzvu. Máme tabuľku so stĺpcami pre mená zamestnancov, dátum začiatku, čas začiatku, dátum ukončenia, čas ukončenia a stav.

Filtrovanie riadkov

Začnime výberom riadkov na základe ich stavu a editor dotazov nám môže pomôcť s touto logikou. Kliknite na šípku rozbaľovacej ponuky vedľa položky Stav. Vybertea vyberte možnosť Nezačína s. 

V okne Filtrovať riadky , ktoré sa zobrazí, napíšte „e“. Potom kliknite na tlačidlo OK .

Zatiaľ sa nič nestane a to je v poriadku, pretože v našich údajoch píšeme malé písmeno „e“ a „Vylúčiť“, ktoré začína veľkým písmenom „E“.

Aby sme to vyriešili, použijeme voliteľný tretí parameter Text.StartsWith . V riadku vzorcov prejdite na koniec funkcie Text.StartsWith. Pridajte čiarku a zadajte naše Comparer.OrdinalIgnoreCase. Kliknite na začiarknutie a tým by sa mali odstrániť riadky Vylúčiť .

Samozrejme, mohli by sme mať niekoho, kto omylom napísal „Vylúčiť“ bez počiatočného „E“. V takom prípade môžeme vytvoriť ďalšiu logiku, ktorá bude zodpovedať situácii. 

Môžeme otestovať, či slovo neobsahuje „X“ rozšírením našej funkcie a použitím Text.Contains . Ak to chcete urobiť, pridajte alebo Text.Contains ([Status], „x“, Comparer.OrdinalIgnoreCase .

Všimnite si, že v porovnaní s prvou vetou sme nahradili „e“ znakom „x“. Stále ignorujeme prípad a oba naše vety zabalíme do zátvoriek. Kliknite na začiarknutie a mali by sme dostať tabuľku bez riadkov obsahujúcich stav Vylúčiť alebo Xclude .

Pridanie vlastného stĺpca

Poďme teraz k hlavnej časti tejto výzvy. Pomocou používateľského rozhrania pridáme vlastný stĺpec a potom sa prepneme do editora. 

Začnite ikonou mini stola v ľavom hornom rohu stola. Kliknite naň a v rozbaľovacej ponuke vyberte možnosť Pridať . 

V tomto bode nás zaujímajú len údaje, ktoré budeme potrebovať neskôr. A keďže budeme pracovať s viacerými poľami, vytvorme záznam pomocou inicializátorov záznamu, reprezentovaných hranatými zátvorkami. 

Poďme vytvoriť premenné a priradiť dostupné stĺpce. Začnite zadaním SD , ktoré sa bude rovnať dátumu začiatku. Môžeme to urobiť kliknutím na Dátum začiatku v stĺpci napravo.

To isté urobíme pre premenné ST, ED a ET , ktoré majú byť priradené k Start Time, Stop Date a Stop Time . Potom uzavrite záznam pomocou uzatváracej zátvorky. Kliknite na tlačidlo OK.

Do tabuľky sa pridá vlastný stĺpec so záznamami. Kliknite na biele miesto vedľa Record a otvorí sa tabla náhľadu. Tým sa zobrazia všetky hodnoty z tohto riadku.

LuckyTemplates Pracovná doba medzi dátumami: Premenné

Potom môžeme naformátovať naše premenné cez okno rozšíreného editora . Takto by malo vyzerať okno rozšíreného editora. 

Potom dajme naše premenné do samostatných nových riadkov.

Teraz môžeme rozšíriť logiku, ktorú potrebujeme. Takže namiesto záznamu by sme tu mohli použiť aj vnorený výraz let. Všimnite si, že jeden z nich bude fungovať, pretože máme viacero premenných alebo názvov polí s priradenými hodnotami.

Náš dátum začiatku je momentálne zapísaný ako textová hodnota a musíme ju transformovať do správneho formátu dátumu. Ak to chcete urobiť, omotajte okolo neho  funkciu Date.FromText .

Pre čas začiatku , ktorý je tiež formátovaný ako reťazec, ho musíme previesť na číslo. Potom ho môžeme vydeliť stovkou a zaokrúhliť, aby sme odstránili všetky desatinné miesta. 

Pomocou funkcie Number.From vydeľte našu hodnotu Dátum začiatku číslom 100 . Potom ich zabaľte do ďalšej funkcie Number.Round a na koniec pridajte 0, čím sa zaokrúhli bez desatinných miest. 

Pre dátum ukončenia a čas ukončenia skopírujeme rovnaké funkcie, aké sme mali v časti Dátum začiatku a Čas začiatku , a nahradíme ich príslušnými premennými.

Keď sú premenné správne naformátované, vytvorte v našom zázname ďalšie pole pre ďalšiu logiku. Vytvorte nový riadok stlačením klávesu Enter. 

Musíme vytvoriť zoznam dátumov od prvého dátumu až po dátum ukončenia. Tieto dátumy nazveme LD a použijeme funkciu Zoznam.Dátumy . 

Prvým parametrom tejto funkcie je dátum začiatku , ktorý by bol náš SD. Potom to chce počítať ako číslo alebo rozdiel medzi dátumom začiatku a dátumom ukončenia. To môžeme získať pomocou funkcie Number.From , za ktorou nasleduje ( ED – SD) + 1 . Všimnite si, že „+1“ sa nezobrazuje na ďalších súboroch snímok obrazovky, ale malo by tam byť +1 .

Tretí parameter sa nazýva krok ako trvanie a chceme prírastok o jeden deň. Môžeme to získať pomocou Duration.From (1). 

Vytvorili sme zoznam s dátumami od úplne prvého dátumu začiatku až po dátum ukončenia. Ďalej chceme vytvoriť zoznam časov, ktoré s tým súvisia. 

Nazvime to LT pre zoznam časov. LT môže mať jednodňové obdobie jedného dňa, čo si vyžaduje trochu inú logiku, ako keby sa rozprestieralo na viacerých dňoch. Takže to je niečo, pre čo musíme vytvoriť podmienky. 

Chceme, aby podmienka hovorila „ak je náš dátum začiatku rovnaký ako dátum ukončenia, potom čas ukončenia mínus čas začiatku.“ Výsledky by mali byť vo formáte zoznamu, takže použijeme inicializátor zoznamu reprezentovaný { } . 

Ak teraz máme rozsah dátumov, ktorý zahŕňa viacero dní, potom prvý dátum bude 24 mínus čas začiatku. Pridáme to k našej podmienke ako vyhlásenie else . Opäť to naformátujte ako zoznam pomocou zložených zátvoriek.

V tomto bode sme nezohľadnili ďalšie celé dni, takže na pridanie viacerých zoznamov použijeme symbol ampersand (&). Pre každý celý deň vytvorte zoznam s hodnotou 24, ktorá predstavuje 24 hodín, ktoré máme v deň. Použijeme na to  List.Repeat .

Pomocou funkcie List.Repeat vytvorte zoznam, ktorý obsahuje 24 a zopakujte ho niekoľkokrát počítaním dní v LD . Aby ste to dosiahli, použite List.Count (LD),   potom odčítajte 2, pretože máme samostatný zoznam pre dátum začiatku a vytvoríme ďalší zoznam pre čas ukončenia. 

V podstate sa tak vytvorí zoznam s iba 24 hodinami na každý celý deň. 

Pre čas ukončenia ho môžeme znova pripojiť ako zoznam pomocou znaku ampersand. Potom pomocou inicializátorov zoznamu zavolajme ET. 

Zatiaľ máme dva veľké zoznamy – zoznam dátumov a zoznam časov – pričom ich dĺžka je rovnaká. Z týchto dvoch zoznamov môžeme zostaviť jednu tabuľku. 

V novom riadku vytvoríme pre tabuľku ďalšiu premennú, ktorú nazveme t a použijeme funkciu Table.FromColumns . Táto funkcia vyžaduje zoznamy ako zoznam a my použijeme náš LD. 

Náš LD obsahuje jednotlivé dátumy od dátumu začiatku až po dátum ukončenia. Tento zoznam dátumov môžeme transformovať na hodnotu dátumu konca mesiaca prejdením LD a volaním funkcie Date.EndOfMonth . Potom v ďalšom riadku získajme aj  náš LT .

Stlačením klávesu Enter vytvorte ďalší riadok a nastavte našu tabuľku ako tabuľku typu . Naša tabuľka bude mať dva stĺpce, pričom prvý stĺpec bude stĺpec dátumu. Takže vytvorte stĺpec Date a zavolajte funkciu Date.Type .

Druhý stĺpec bude náš stĺpec Hodiny a bude to celé číslo. Preto vytvoríme stĺpec hodín a zavoláme Int8.Type.

Máme uzatváraciu zátvorku na zatvorenie nášho záznamu a uzatváraciu zátvorku na zatvorenie našej funkcie Table.AddColumn . Kliknite na Hotovo a zobrazí sa zoznam záznamov v stĺpci Vlastné. 

Opäť kliknite na biele miesto vedľa každého záznamu a tabuľka by sa mala otvoriť v spodnej časti obrazovky. 

Všimnite si, že našu tabuľku t máme v poslednom riadku. Skúsme sa prevŕtať do jedného z Záznamov. Kliknite pravým tlačidlom myši na prvý výsledok a vyberte Pridať ako nový dotaz . 

Dostaneme rovnaký súhrn ako predtým a ak klikneme na Tabuľku , mali by sme získať hodnoty Dátum a Hodiny .

Nás ale v prvom rade zaujíma výsledná výstupná tabuľka. Aby sme sa na to mohli zamerať, odstránime dotaz pomocníka kliknutím pravým tlačidlom myši na položku Vlastné a výberom položky Odstrániť . Vo vyskakovacom okne potvrďte kliknutím na Odstrániť .

Po odstránení sa môžeme vrátiť k nášmu pôvodnému dotazu a znova kliknúť na prázdne miesto vedľa Record. Ukáže rovnaké výsledky ako predtým. Tentokrát však rozbaľme riadok vzorcov a zavolajme [t] vedľa našich zátvoriek.

V stĺpci Vlastné vidíme, že každý záznam sa zmení na Tabuľka . 

LuckyTemplates Pracovná doba medzi dátumami: Projekcia

V tabuľke, na ktorú sa pozeráme, sú len dva zaujímavé stĺpce – meno zamestnanca a náš vlastný stĺpec. Môžeme použiť projekciu na zachovanie týchto dvoch stĺpcov samostatne z tejto tabuľky. 

Prejdite na riadok vzorcov a vedľa našej záverečnej zátvorky vyberte polia, ktoré chceme zachovať, umiestnením do hranatých zátvoriek. Potom stlačte začiarknutie.

Tento výsledok by sme mali dostať.

Kliknutím na ikony šípok  vedľa položky Custom . Zrušte začiarknutie políčka Použiť pôvodný názov stĺpca ako predponu a kliknite na tlačidlo OK.

Naša tabuľka by mala vyzerať takto.

LuckyTemplates Pracovná doba medzi dátumami: Súhrnné hodnoty

Teraz už zostáva len agregovať tieto hodnoty. Ak to chcete urobiť, vyberte stĺpec Staff Name a prejdite na Transform. 

Vo vyskakovacom okne vyberte Hodiny v stĺpci Hodnoty , pretože to je to, čo chceme agregovať. Potom vyberte Súčet pod . Potom kliknite na tlačidlo OK.

Náš konečný výstup bude vyzerať takto. 

Záver

V tomto blogu ste videli jeden prístup k výzve Start Stop Challenge v Access Analytic. Naučili ste sa vypočítať celkový počet odpracovaných hodín každého zamestnanca za mesiac a ako sa vysporiadať s možnými preklepmi v údajoch. 

Pomocou tejto techniky a LuckyTemplates je možné jednoducho vypočítať pracovné hodiny medzi dátumami vo vašej vlastnej organizácii alebo ako prax na prehĺbenie vašich vedomostí a zručností LuckyTemplates.