Variable Help

[#1]

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!

[CELAMBARASAN]

Hi,

     Check this link.

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

Hope it solves your problem

Celambarasan

[Oleg_Troyansky · Accepted solution]

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

[#4]

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!