Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi
Try like below
=Date(MonthStart(Date#(Left(Filebasename(),10),'YYYY-MM-DD')),'YYYYMMDD')
Hi
Try like below
=Date(MonthStart(Date#(Left(Filebasename(),10),'YYYY-MM-DD')),'YYYYMMDD')
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
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.
Hi Mayil,
I am sorry, didn't understand what you mean. Please explain again.
Thank you
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.
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 🙂
Thank you Mayil and Richard, both are working.