Month start date key based on FileBaseName

[ngreddy1982]

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? 

[MayilVahanan · Accepted solution]

Hi

Try like below

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

[ngreddy1982]

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 

[MayilVahanan]

Hi 

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.

[ngreddy1982]

Hi Mayil,

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

Thank you

[MayilVahanan]

Hi

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.

[richard_chilvers]

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 🙂

[ngreddy1982]

Thank you Mayil and Richard,  both are working.