LuckyTemplates päivämäärien välillä työaikaratkaisu

Tämän päivän blogissa esittelen lähestymistavan Access Analyticin Start Stop Challenge -haasteeseen , jossa LuckyTemplatesin avulla lasketaan päivien väliset kokonaistyötunnit. Voit katsoa tämän opetusohjelman koko videon tämän blogin alaosasta .

Tehtävänä on laskea työntekijän työtuntien kokonaismäärä joka kuukausi ja päivämäärä. Esitin aloitus- ja lopetuspäivät, jotka voivat ylittää kuukausia ja ottaa tilan huomioon mahdollisten kirjoitusvirheiden käsittelyssä. 

Sisällysluettelo

• Tietojoukko LuckyTemplatesille päivämäärien työajan välillä
• Rivien suodatus
• Mukautetun sarakkeen lisääminen
• LuckyTemplates Between-Dates Työajat: Muuttujat
• LuckyTemplates Between-Dates Työajat: Projektio
• LuckyTemplates Between-Dates Working Hours: Aggregate Values
• Johtopäätös

Tietojoukko LuckyTemplatesille päivämäärien työajan välillä

Siirrytään Power Query -editoriin. 

Tässä on datamme tätä haastetta varten. Meillä on taulukko, jossa on sarakkeet henkilöstön nimelle, aloituspäivälle, alkamisajalle, lopetuspäivämäärälle, lopetusajalle ja tilalle.

Rivien suodatus

Aloitetaan valitsemalla rivit niiden tilan perusteella, ja Kyselyeditori voi auttaa meitä tässä logiikassa. Napsauta Tila-kohdan vieressä olevaa pudotusvalikon nuolta . Valitseja valitse Ei aloita. 

Kirjoita näkyviin tulevaan Filter Rows -ikkunaan "e". Napsauta sitten OK .

Mitään ei tapahdu vielä, ja se on hyvä, koska kirjoitamme tietoihimme pienen e-kirjaimen ja isolla E-kirjaimella alkavan poissulkemisen.

Korjaamme tämän käyttämällä valinnaista kolmatta parametria Text.StartsWith . Siirry kaavapalkissa Text.StartsWith-funktion loppuun. Lisää pilkku ja syötetään Comparer.OrdinalIgnoreCase. Napsauta valintamerkkiä ja sen pitäisi poistaa poissulkevat rivit.

Tietysti meillä voi olla joku, joka on kirjoittanut virheellisesti "Exclude" ilman alkukirjainta E. Siinä tapauksessa voimme luoda lisälogiikkaa tilanteen mukaan. 

Voimme testata, onko sanassa "X" laajentamalla toimintoamme ja käyttämällä Text.Contains . Voit tehdä tämän lisäämällä tai Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Huomaa, että verrattuna ensimmäiseen lauseeseen korvasimme "e":llä "x". Jätämme tapauksen huomioimatta ja käärimme sulkujen joukon molempien lauseiden ympärille. Napsauta valintamerkkiä ja meidän pitäisi saada taulukko ilman rivejä, jotka sisältävät poissulkemis- tai poissulkemistilan .

Mukautetun sarakkeen lisääminen

Siirrytään nyt tämän haasteen pääosaan. Lisäämme mukautetun sarakkeen käyttöliittymän avulla ja siirrymme sitten editoriin. 

Aloita minipöytäkuvakkeella taulukon vasemmassa yläkulmassa. Napsauta sitä ja valitse avattavasta valikosta Lisää . 

Tässä vaiheessa olemme vain kiinnostuneita tuomaan tiedot, joita tarvitsemme myöhemmin. Ja koska työskentelemme useiden kenttien kanssa, luodaan tietue käyttämällä tietueen alustuslaitteita, jotka on esitetty hakasulkeilla. 

Luodaan muuttujia ja määritetään käytettävissä olevat sarakkeet. Aloita kirjoittamalla SD , joka rinnastetaan aloituspäivämäärään. Voimme tehdä sen napsauttamalla aloituspäivämäärää oikeanpuoleisesta sarakkeesta.

Teemme samoin muuttujille ST, ED ja ET , jotka määritetään aloitusajalle , lopetuspäivämäärälle ja lopetusajalle . Sulje sitten tallennus sulkusululla. Napsauta OK.

Mukautettu sarake tietueineen lisätään taulukkoon. Napsauta Tallenna- kohdan vieressä olevaa tyhjää tilaa ja esikatseluruutu avautuu. Tämä tuo kaikki tämän rivin arvot.

LuckyTemplates Between-Dates Työajat: Muuttujat

Voimme sitten muotoilla muuttujiamme Advanced Editor -ikkunan kautta. Tältä Advanced Editor -ikkunan pitäisi näyttää. 

Laitetaan sitten muuttujamme erillisiin uusiin riveihin.

Nyt voimme laajentaa tarvitsemamme logiikkaa. Joten tietueen sijasta voisimme käyttää tässä myös sisäkkäistä let-lauseketta. Huomaa, että jompikumpi näistä toimii, koska meillä on useita muuttujia tai kenttien nimiä ja niille on määritetty arvoja.

Aloituspäivämäärämme on tällä hetkellä kirjoitettu tekstiarvona, ja meidän on muutettava se oikeaan päivämäärämuotoon . Tee tämä käärimällä sen ympärille  funktio Date.FromText .

Aloitusaikaa varten , joka on myös muotoiltu merkkijonoksi, meidän on muutettava se numeroksi. Sitten voimme jakaa sen sadalla ja pyöristää sen poistamaan desimaalit. 

Käytä Number.From- funktiota ja jaa aloituspäivämäärämme arvo 100 : lla . Kääri ne sitten seuraavan funktion, Number.Round , sisään ja lisää loppuun , 0 pyöristääksesi ilman desimaaleja. 

Lopetuspäivä ja lopetusaika -kohdassa kopioimme samat funktiot, jotka meillä oli aloituspäivämäärässä ja aloitusajassa , ja korvaamme ne asianmukaisilla muuttujilla.

Kun muuttujat on muotoiltu oikein, luo tietueeseemme toinen kenttä lisälogiikkaa varten. Luo uusi rivi painamalla Enter. 

Meidän on laadittava luettelo päivämääristä ensimmäisestä päivämäärästä aina lopetuspäivään. Kutsumme näitä päivämääriä LD:ksi ja käytämme funktiota List.Dates . 

Tämän funktion ensimmäinen parametri on aloituspäivämäärä, joka olisi SD. Sitten se haluaa laskea luvun tai eron aloituspäivän ja lopetuspäivän välillä. Voimme saada sen käyttämällä Number.From- funktiota, jota seuraa ( ED – SD) + 1 . Huomaa, että "+1" ei näy seuraavissa kuvakaappauksissa, mutta +1 :n pitäisi olla .

Kolmatta parametria kutsutaan askeleeksi kestona ja haluamme yhden päivän lisäyksen. Voimme saada sen käyttämällä Duration.From (1). 

Olemme luoneet luettelon, jossa on päivämäärät ensimmäisestä aloituspäivästä lopetuspäivään. Haluamme seuraavaksi luoda luettelon ajoista, jotka liittyvät siihen. 

Kutsutaan tätä LT: ksi lista-ajoista. LT:llä voi olla yhden päivän vuorokausi, mikä vaatii hieman erilaista logiikkaa kuin jos se ulottuu useille päiville. Joten se on jotain, jolle meidän on luotava ehto. 

Haluamme ehdon sanovan "jos aloituspäivämäärämme on sama kuin lopetuspäivämäärämme, lopetusaika miinus aloitusaika." Tulosten tulee olla luettelomuodossa, joten käytämme luettelon alustusta , jota edustaa { } . 

Nyt, jos meillä on ajanjakso, joka kattaa useita päiviä, ensimmäinen päivämäärä on 24 miinus aloitusaika. Lisäämme tämän ehtoomme muuna lausumana . Muotoile se jälleen luetteloksi kiharasulkeilla.

Tässä vaiheessa emme ole ottaneet huomioon muita kokonaisia ​​päiviä, joten käytämme et-merkkiä (&) useiden luetteloiden liittämiseen. Luo jokaiselle kokonaiselle päivälle luettelo, jonka arvo on 24, joka edustaa vuorokauden 24 tuntia. Käytämme tähän  List.Repeat-toimintoa .

Luo List.Repeat - toiminnolla luettelo, joka sisältää 24, ja toista se useita kertoja laskemalla päivien lukumäärä LD :ssä . Tämän saavuttamiseksi käytä List.Count (LD)   -funktiota ja vähennä sitten 2, koska meillä on erillinen luettelo aloituspäivällemme ja luomme toisen luettelon lopetusajalle. 

Pohjimmiltaan tämä luo luettelon, jossa on vain 24 tuntia jokaista koko päivää kohden. 

Lopetusaikaa varten voimme liittää sen uudelleen luettelona käyttämällä et-merkkiä. Tämän jälkeen kutsutaan ET:ksi luettelon alustustoimintojen avulla. 

Toistaiseksi meillä on kaksi suurta luetteloa - luettelo päivämääristä ja aikojen luettelo - joiden pituus on sama. Näistä kahdesta luettelosta voimme rakentaa yhden taulukon. 

Luo uudelle riville taulukolle toinen muuttuja, jota kutsumme t ja käytä funktiota Table.FromColumns . Tämä toiminto vaatii luetteloita luettelona ja käytämme LD:tä. 

LD sisältää yksittäisiä päivämääriä aloituspäivästä lopetuspäivään . Voimme muuttaa tämän päivämääräluettelon kuun lopun päivämäärän arvoksi välittämällä LD:n ja kutsumalla Date.EndOfMonth -funktion. Sitten seuraavalla rivillä hankitaan myös  LT .

Luo uusi rivi painamalla Enter ja aseta taulukko tyyppitaulukoksi . Taulukossamme on kaksi saraketta, joista ensimmäinen on päivämääräsarake. Luo siis Date- sarake ja kutsu Date.Type -funktio.

Toinen sarake on Tunti- sarakkeemme ja se on kokonaisluku. Luomme siis Hrs- sarakkeen ja kutsumme Int8.Typeä.

Meillä on loppusulkeet tietueen sulkemiseksi ja sulkevat sulut Table.AddColumn -toiminnon sulkemiseksi. Napsauta Valmis ja saamme luettelon tietueista mukautetun sarakkeen alle. 

Napsauta jälleen jokaisen tietueen vieressä olevaa valkoista tilaa , jolloin taulukon pitäisi avautua näytön alaosaan. 

Huomaa, että meillä on t-taulukko viimeisellä rivillä. Yritetään porautua yhteen Recordsista. Napsauta hiiren kakkospainikkeella ensimmäistä tulosta ja Lisää uutena kyselynä . 

Saamme saman yhteenvedon kuin aiemmin, ja jos napsautamme Taulukkoa , meidän pitäisi saada päivämäärä- ja tuntiarvomme .

Mutta olemme ensisijaisesti kiinnostuneita lopullisesta tulostaulukosta. Keskittyäksesi tähän, poistetaan ohjekysely napsauttamalla hiiren kakkospainikkeella Mukautettu ja valitsemalla Poista . Vahvista napsauttamalla ponnahdusikkunassa Poista .

Kun se on poistettu, voimme palata alkuperäiseen kyselyymme ja napsauttaa uudelleen Tallenna-kohdan vieressä olevaa tyhjää tilaa. Se näyttää samat tulokset kuin aiemmin. Mutta tällä kertaa laajennataan kaavapalkkia ja kutsutaan [t] sulkumerkkiemme viereen.

Mukautettu-sarakkeessa näemme, että jokainen tietue on muutettu taulukoksi . 

LuckyTemplates Between-Dates Työajat: Projektio

Tarkastelemassamme taulukossa on vain kaksi kiinnostavaa saraketta – henkilöstön nimi ja mukautettu sarake. Voimme käyttää projektiota säilyttääksemme nämä kaksi saraketta yksin tästä taulukosta. 

Siirry kaavapalkkiin ja valitse sulkevien sulkeiden vierestä kentät, jotka haluamme säilyttää, asettamalla ne hakasulkeisiin. Paina sitten valintamerkkiä.

Meidän pitäisi saada tämä tulos.

Voimme laajentaa napsauttamalla Mukautettu-kohdan  vieressä olevia nuolikuvakkeita . Poista valinta Käytä alkuperäistä sarakkeen nimeä etuliitteenä ja napsauta OK.

Tauluksemme pitäisi näyttää tältä.

LuckyTemplates Between-Dates Working Hours: Aggregate Values

Nyt ei tarvitse tehdä muuta kuin koota nämä arvot yhteen. Voit tehdä tämän valitsemalla Henkilökunnan nimi -sarakkeen ja siirtymällä kohtaan Muutos. 

Valitse ponnahdusikkunassa Arvot -sarakkeesta Tuntia , koska haluamme koota sen. Valitse sitten Summa kohdasta . Napsauta sitten OK.

Lopputulos näyttää tältä. 

Johtopäätös

Tässä blogissa olet nähnyt yhden lähestymistavan Access Analyticin Start Stop Challenge -haasteeseen. Opit laskemaan kunkin työntekijän työtuntien kokonaismäärän kuukaudessa ja kuinka käsitellä mahdollisia kirjoitusvirheitä tiedoissasi. 

Tällä tekniikalla ja LuckyTemplatesilla voit helposti laskea päivämäärien välisiä työtunteja omassa organisaatiossasi tai käytäntönä syventää LuckyTemplates-tietojasi ja -taitojasi.