Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Can someone help me with my min and max date variable problem?
I have created min and max date variables in script:
Temp:
LOAD
Min([Date]) as MinDate,
Max([Date]) as MaxDate
Resident Table1;
LET vMinDate = date(floor(peek('MinDate')));
LET vMaxDate = date(floor(peek('MaxDate')));
DROP Table Temp;
When checking their valus in Input Box, vMinDate = 28/02/2017 and vMaxDate = 01/03/2015 (which is correct).
After that I wanted to assign this values where necessary in the table bellow but instead I got a decimal number which corresponds to 30/12/1899 (for both min and max date).
Store name | Status_New | New_StartDate | New_EndDate | Status_Intermediate | Intermediate_StartDate | Intermediate_EndDate | Status_Old | Old_StartDate | Old_EndDate |
Store1 | Old | 0.0069410014873575 | |||||||
Store2 | New | 0.00016542597187758 | 0.00016542597187758 | ||||||
Store3 | New | 0.00016542597187758 | 30/05/2015 | Intermediate | 01/06/2015 | 31/01/2016 | Old | 01/02/2016 | 0.0069410014873575 |
Store4 | Old | 0.0069410014873575 |
This is the script I used for putting in min and max dates:
Stores_temp1:
LOAD [Store name],
Status_New,
if(Status_New='New', $(vMinDate), date(num#(New_StartDate), 'DD/MM/YYYY')) as New_StartDate,
if(len(Status_Intermediate)=0 and Status_New='New', $(vMaxDate), date(num#(New_EndDate), 'DD/MM/YYYY')) as New_EndDate,
Status_Intermediate,
if(len(Status_New)=0 and Status_Intermediate='Intermediate', $(vMinDate), date(num#(Intermediate_StartDate), 'DD/MM/YYYY')) as Intermediate_StartDate,
if(len(Status_Old)=0 and Status_Intermediate='Intermediate', $(vMaxDate), date(num#(Intermediate_EndDate), 'DD/MM/YYYY')) as Intermediate_EndDate,
Status_Old,
if(len(Status_Intermediate)=0 and Old_StartDate='Old', $(vMinDate), date(num#(Old_StartDate), 'DD/MM/YYYY')) as Old_StartDate,
if(Status_Old='Old', $(vMaxDate), date(num#(Old_EndDate), 'DD/MM/YYYY')) as Old_EndDate
FROM
$(vDataPath)\file1.xlsx
(ooxml, embedded labels, table is Stores);
Sorry for the long post.
Thanks for your help.
May be try with single quotes around your variable
Stores_temp1:
LOAD [Store name],
Status_New,
if(Status_New='New', '$(vMinDate)', date(num#(New_StartDate), 'DD/MM/YYYY')) as New_StartDate,
if(len(Status_Intermediate)=0 and Status_New='New', '$(vMaxDate)', date(num#(New_EndDate), 'DD/MM/YYYY')) as New_EndDate,
Status_Intermediate,
if(len(Status_New)=0 and Status_Intermediate='Intermediate', '$(vMinDate)', date(num#(Intermediate_StartDate), 'DD/MM/YYYY')) as Intermediate_StartDate,
if(len(Status_Old)=0 and Status_Intermediate='Intermediate', '$(vMaxDate)', date(num#(Intermediate_EndDate), 'DD/MM/YYYY')) as Intermediate_EndDate,
Status_Old,
if(len(Status_Intermediate)=0 and Old_StartDate='Old', '$(vMinDate)', date(num#(Old_StartDate), 'DD/MM/YYYY')) as Old_StartDate,
if(Status_Old='Old', '$(vMaxDate)', date(num#(Old_EndDate), 'DD/MM/YYYY')) as Old_EndDate
FROM
$(vDataPath)\file1.xlsx
(ooxml, embedded labels, table is Stores);
A field is a list of values (even if the list only contains 1 value), a variable only 1.
So you can't put a list into a field. That is like trying to put 500 people in 1 chair.
So what you have to do is tell Qlik which value of that list you want to have in your variable. This can be done by the Peek() function for example.
Let vMinDate = NUM(PEEK('MinDate' , 0, 'Temp'));
Let vMaxDate = NUM(PEEK('MaxDate' , 0, 'Temp'));
In Qlik:
Date(0) = December 30, 1899
Date(1) = December 31, 1899
Date(2) = January 1, 1900
This is different then in all Microsoft products. But no worries, from March 1st 1900 the numbers between Qlik and Microsoft are the same again.
O... and before you might make the wrong assumption... Microsoft made the mistake, not Qlik. This number difference has to do with February 29th 1900... which does not exist. Qlik just corrected the mistake so you can correctly calculate with dates.
The problem is that my variables vMinDate and vMaxDate seem to be working just fine in Input Box, but when placing those variables in the table they sudenly change to minimum date in Qlik (at the same time I can see 2 different values from the same variable - one in Input Bos, another one in table). And I don't understand why this is happening and how to fix it.
May be try with single quotes around your variable
Stores_temp1:
LOAD [Store name],
Status_New,
if(Status_New='New', '$(vMinDate)', date(num#(New_StartDate), 'DD/MM/YYYY')) as New_StartDate,
if(len(Status_Intermediate)=0 and Status_New='New', '$(vMaxDate)', date(num#(New_EndDate), 'DD/MM/YYYY')) as New_EndDate,
Status_Intermediate,
if(len(Status_New)=0 and Status_Intermediate='Intermediate', '$(vMinDate)', date(num#(Intermediate_StartDate), 'DD/MM/YYYY')) as Intermediate_StartDate,
if(len(Status_Old)=0 and Status_Intermediate='Intermediate', '$(vMaxDate)', date(num#(Intermediate_EndDate), 'DD/MM/YYYY')) as Intermediate_EndDate,
Status_Old,
if(len(Status_Intermediate)=0 and Old_StartDate='Old', '$(vMinDate)', date(num#(Old_StartDate), 'DD/MM/YYYY')) as Old_StartDate,
if(Status_Old='Old', '$(vMaxDate)', date(num#(Old_EndDate), 'DD/MM/YYYY')) as Old_EndDate
FROM
$(vDataPath)\file1.xlsx
(ooxml, embedded labels, table is Stores);
Hi
The values of the dates are to small because Qlik is evaluating the expression 28/02/2017 (dividing 28 by 2 and the result by 2017)
Is necessary to use it as string or do the conversion from date to num at the begining and use the Date function to show the value to the user.
Regards
Please try changing above script in this way.
Temp:
LOAD
Min([Date]) as MinDate,
Max([Date]) as MaxDate
Resident Table1;
LET vMinDate = num(date(floor(peek('MinDate'))));
LET vMaxDate = num(date(floor(peek('MaxDate'))));
DROP Table Temp;
And use Date() in your text box to see if it is showing correctly or not.
Thank you for your help. Now everything is working just fine.
Just one more question. What do the single quotes do and when should I use them?
Peek function is reading your date as text and in order to use text in a field you need to wrap it around with single quotes. The alternative here could be to use remove date and leave only the floor function in your peek and then when you call it, use Date($(vMaxDate))