topic Re: Load WHERE = vVariable in QlikView

Load WHERE = vVariable

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

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

;

Re: Load WHERE = vVariable

vishsaggi — Tue, 15 May 2018 19:51:02 GMT

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

Re: Load WHERE = vVariable

vkish16161 — Tue, 15 May 2018 19:54:04 GMT

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

Re: Load WHERE = vVariable

sasiparupudi1 — Wed, 16 May 2018 08:48:50 GMT

May be try

[Controls]:

LOAD Date(CheckDate) as MaxControlDate,

Recon

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

Where CheckDate = Num($(vMaxCtrlDate))

Re: Load WHERE = vVariable

greend21 — Wed, 16 May 2018 12:06:59 GMT

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

Re: Load WHERE = vVariable

greend21 — Wed, 16 May 2018 12:11:05 GMT

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?

Re: Load WHERE = vVariable

olivierrobin — Wed, 16 May 2018 12:11:39 GMT

hello

I would try without date function

Load Max([CheckDate]) as MaxControlDate

.....

Where CheckDate = $(vMaxCtrlDate)

so that dates are in a numeric form

Re: Load WHERE = vVariable

bc-thebruuu — Wed, 16 May 2018 12:14:45 GMT

Can you share app and QVD to play around?

Re: Load WHERE = vVariable

greend21 — Wed, 16 May 2018 12:31:56 GMT

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.

Re: Load WHERE = vVariable

greend21 — Wed, 16 May 2018 12:43:23 GMT

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

Re: Load WHERE = vVariable

greend21 — Wed, 16 May 2018 12:44:51 GMT

I ended up going a different route where I'm loading in the last 3 days to limit the data and always allow for there to be info in the load and just use set analysis in my KPI.

=Sum({<CheckDate={'$(=Max(CheckDate))'}>}Recon)

*I'm marking this as correct since I no longer need help, but it does not solve the original issue.*

Re: Load WHERE = vVariable

vishsaggi — Wed, 16 May 2018 14:21:48 GMT

Did you try preceding load like below:

Can we know your date format you are getting in your Check Date field?

Temp:

Load Floor(Max(Date#([Check Date], 'M/DD/YYYY hh:mm:ss'))) as MaxControlDate

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

LET vMaxCtrlDate = Peek('MaxControlDate');

Drop Table Temp;

[Controls]:

LOAD *

WHERE MaxControlDate = '$(vMaxCtrlDate)';

LOAD Date([Check Date]) AS MaxCheckDate,

Floor(Max(Date#([Check Date], 'M/DD/YYYY hh:mm:ss'))) as MaxControlDate,

Recon

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