Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Min and Max date 30/12/1899

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 nameStatus_NewNew_StartDateNew_EndDateStatus_IntermediateIntermediate_StartDateIntermediate_EndDateStatus_OldOld_StartDateOld_EndDate
Store1 Old 0.0069410014873575
Store2New0.000165425971877580.00016542597187758
Store3New0.0001654259718775830/05/2015Intermediate01/06/201531/01/2016Old01/02/20160.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.

1 Solution

Accepted Solutions
sunny_talwar

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);

View solution in original post

7 Replies
oknotsen
Master III
Master III

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.

May you live in interesting times!
Anonymous
Not applicable
Author

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.

sunny_talwar

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);

jmvilaplanap
Specialist
Specialist

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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?

sunny_talwar

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))