topic Variable Help in QlikView

Variable Help

Tue, 08 May 2012 13:38:07 GMT

I am trying to limit data is a table to data that is within a date range from another table. I have setup a min and max variable (minNote and maxNote) to hold the information.

SET minNote='X';

SET maxNote='Y';

LET minNote=(Now()-(365*2));

LET maxNote=Now();

The initial LET statements default the data to something useful.

I then load the source data:

Qualify *;

HAgent:

LOAD row_date

FROM

$(vAvayaCMSDataPath)HAGENT.qvd

(qvd);

Then try to set the variables up with the dynamic information:

LET minNote=Min(HAgent.row_date);

LET maxNote=Max(HAgent.row_date);

But they end up blank...

Finally I query the end table:

AFINotes:

LOAD AFI_NOTE_TEXT

FROM

$(vAFISystemDataPath)AFINote.qvd

(qvd) Where AFI_NOTE_EFF_DATE >= Date('$(minNote)') AND AFI_NOTE_EFF_DATE <= Date('$(maxNote)');

Functions if the following is commented out:

LET minNote=Min(HAgent.row_date);

LET maxNote=Max(HAgent.row_date);

But pulls more data than I want.

How can I use information from one table to filter information from another on load?

Thanks!

Variable Help

CELAMBARASAN — Tue, 08 May 2012 13:59:00 GMT

Hi,

Check this link.

http://community.qlik.com/message/210096

Hope it solves your problem

Celambarasan

Variable Help

Oleg_Troyansky — Tue, 08 May 2012 14:22:50 GMT

Jim,

functions Min() and Max() are aggregated functions and they can only be used in aggregated LOAD statements. You can't simply use them in the LET statement. In order to calculate the min/max date, you should follow those teps:

1. LOAD your data

2. Aggregate the data and claculate min/max:

LOAD

min(Date) as MinDate,

max(DAte) as MaxDate

RESIDENT

MyData

3. Use function PEEK() to retreive the field values and assign them to the variables:

LET vMinDate = peek('MinDate');

...

Notice that when you apply functions like min, max, or any arithmetic operations, the result becomes numeric, as opposed to dual. You may need to format the results using function DATE() in order to restore the original date formatting.

cheers,

Oleg

Variable Help

Tue, 08 May 2012 14:50:44 GMT

That's it. I replaced:

LET minNote=Min(HAgent.row_date);

LET maxNote=Max(HAgent.row_date);

With:

TmpMinAndMax:

LOAD

Min(HAgent.row_date) as MinDate,

Max(HAgent.row_date) as MaxDate

RESIDENT

HAgent;

LET minNote=Peek('TmpMinAndMax.MinDate');

LET maxNote=Peek('TmpMinAndMax.MaxDate');

DROP Table TmpMinAndMax;

and everything worked as expected. To the people out there less familiar with Qlikview; notice on the Peek function the requirement to provide the field name as a string (enclosed in apostrophes).

Thanks again!