Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zagzebski
Contributor

Date not working in where clause

In the script below I can't get the where clause in the "Retention Customer" table to work.

The variable vRetentionDateProduction = 1/1/2013.

If I hard code into my where clause:  Where ProdGLMonth <= makedate(2013,01,01) it works fine.  But for some reason it won't intepret the variable (vRetentionDateProduction) accurately. Am I doing something wrong?

Production_MaxDate:
LOAD
Max(Date([ProdGLMonth])) as vRetentionDateProduction
Resident Production;

LET vRetentionDateProduction = Peek('vRetentionDateProduction', 0, 'Production_MaxDate');

Retention_Customer:
Load
sum([Commission]) as [Commission Cube Pre]
Resident Production
Where ProdGLMonth <= $(vRetentionDateProduction);

Thanks, Steve

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Date not working in where clause

Try quoting your variable:

Where ProdGLMonth <= '$(vRetentionDateProduction)';


or use a number:


LET vRetentionDateProduction = num(Peek('vRetentionDateProduction', 0, 'Production_MaxDate'));

4 Replies
MVP
MVP

Re: Date not working in where clause

Try quoting your variable:

Where ProdGLMonth <= '$(vRetentionDateProduction)';


or use a number:


LET vRetentionDateProduction = num(Peek('vRetentionDateProduction', 0, 'Production_MaxDate'));

zagzebski
Contributor

Re: Date not working in where clause

Thanks, that was my problem!

Steve

zagzebski
Contributor

Re: Date not working in where clause

Figured I would try to hit you up on a related issue if thats ok ...

In the following statement...

 

year($(vRetentionDateProduction))& month($(vRetentionDateProduction))

All I get is 1899Dec for the above. Obviously I a doing something wrong. Since the variable above equals 10/1/2013 I was hoping to return the year month combintion of ....201310

MVP
MVP

Re: Date not working in where clause

If your default date format is set correctly, quoting the variable should also work here (because QV then can interpret your string as date number, which is expected by the date functions year() and month():

year('$(vRetentionDateProduction)')& month('$(vRetentionDateProduction)')

If you used the second suggested approach using the num() function in the variable definition, it should work without quoting.

Community Browser