Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
greend21
Contributor II

Load WHERE = vVariable

I have this script that sets my variable, and if I check the variable within the program I see it is correct, in this case 5/15/18.

Temp:

Load Max(Date([CheckDate])) as MaxControlDate

FROM [lib://QVD/Controls.qvd](qvd);

LET vMaxCtrlDate = Peek('MaxControlDate');

Drop Table Temp;

I've been trying other things I've seen online but can't seem to get it right. I only want to load the info where the CheckDate is = to vMaxCtrlDate. Would anyone know how I get this to work? Each thing I try results in 0 records being loaded.

[Controls]:

LOAD Date(CheckDate) as MaxControlDate,

Recon

FROM [lib://QVD/Controls.qvd](qvd)

Where Date(CheckDate) = '$(=$(vMaxCtrlDate))'

;

11 Replies
vishsaggi
Esteemed Contributor III

Re: Load WHERE = vVariable

Did you try this? Make sure your Checkdate and vMaxCtrlDate are in same format.

LET vMaxCtrlDate = Date(Peek('MaxControlDate'));

[Controls]:

LOAD Date(CheckDate) as MaxControlDate,

Recon

FROM [lib://QVD/Controls.qvd](qvd)

Where Date(CheckDate) = '$(vMaxCtrlDate)';

vkish16161
Contributor III

Re: Load WHERE = vVariable

To avoid discrepancies, convert the date to a number. This'll be easy for you to understand.

like so Num(Date(Insert_Date_Here)) = some number 45421

E.g. Num(Date(5/15/2018)) = 43235

sasiparupudi1
Honored Contributor III

Re: Load WHERE = vVariable

May be try

[Controls]:

LOAD Date(CheckDate) as MaxControlDate,

Recon

FROM [lib://QVD/Controls.qvd](qvd)

Where CheckDate = Num($(vMaxCtrlDate))

greend21
Contributor II

Re: Load WHERE = vVariable

Yes, I tried that and it does not load anything.

greend21
Contributor II

Re: Load WHERE = vVariable

The issue with this is the records I'm loading in are date and timestamp. I'm converting to date to drop the timestamp. Or does this not truly drop the timestamp?

olivierrobin
Valued Contributor III

Re: Load WHERE = vVariable

hello

I would try without date function

Load Max([CheckDate]) as MaxControlDate

.....

Where CheckDate = $(vMaxCtrlDate)

so that dates are in a numeric form

bcavestro
Contributor

Re: Load WHERE = vVariable

Can you share app and QVD to play around?

greend21
Contributor II

Re: Load WHERE = vVariable

I've changed the vairable to be Floor(Max(Checkdate) so it stays in number form and loses the decimal from the timestamp. I'm still having trouble with the Where clause though.

greend21
Contributor II

Re: Load WHERE = vVariable

I was still having issues. I think with the way it is reading the variable in the script for some reason.