topic Re: Month start date key based on FileBaseName in QlikView

Month start date key based on FileBaseName

ngreddy1982 — Wed, 29 Sep 2021 22:38:18 GMT

Hi All,

I have an excel file with the name 2021-09-29_Item, I am trying to create MonthStartDateKey

I have tried the below :

Date(MonthStart(Date(Date#(Left(Filebasename(),10),'YYYY-MM-DD'),'YYYYMMDD')))) as MonthStartDateKey which gives 01/09/2021 completely different format

I am looking for 20210901.

How can I get to that point so that I can join to master calendar?

Re: Month start date key based on FileBaseName

MayilVahanan — Thu, 30 Sep 2021 01:45:46 GMT

Try like below

=Date(MonthStart(Date#(Left(Filebasename(),10),'YYYY-MM-DD')),'YYYYMMDD')

Re: Month start date key based on FileBaseName

ngreddy1982 — Thu, 30 Sep 2021 06:57:11 GMT

Hi Mayil,

In the QVD the values are in the format I am looking for but when I load into QVW the values are not joining to master calendar. Values look like below,

44378
44409
44440

DateKey in the master calendar is Number (20210901), maybe this is passing as text! any thoughts?

Thank you

Re: Month start date key based on FileBaseName

MayilVahanan — Thu, 30 Sep 2021 07:11:51 GMT

Convert both into same format.

Floor(Date) in QVD

Floor(Date#(20210901, 'YYYYMMDD')) in Calendar if its string format.

So both will be number format, it will be map.

Re: Month start date key based on FileBaseName

ngreddy1982 — Thu, 30 Sep 2021 07:17:48 GMT

Hi Mayil,

I am sorry, didn't understand what you mean. Please explain again.

Thank you

Re: Month start date key based on FileBaseName

MayilVahanan — Thu, 30 Sep 2021 07:33:30 GMT

Am trying to say like below. In Qlik, if 2 fields are same name and same format, it will map and give expected results.

As you mentioned, QVD & Calendar are in different format. Try to have both in same format. For that, you can convert the date into number or in same format (date format).

In the QVD, Load like below

Floor(Date) as Date

in Calendar if its string format, convert like below

Floor(Date#(urfield, 'YYYYMMDD')) as Date

Now, both are in same format & it will give expected results.

Re: Month start date key based on FileBaseName

richard_chilvers — Thu, 30 Sep 2021 08:11:46 GMT

Filebasename() will be a text field. Since you always want the first day of the month, keep it simple with:

MonthStartDateKey = Left(Filebasename(),4)&Mid(Filebasename(),5,2)&'01'

Try not use dates as key fields 🙂

Re: Month start date key based on FileBaseName

ngreddy1982 — Thu, 30 Sep 2021 16:42:55 GMT

Thank you Mayil and Richard, both are working.