topic Re: Logic Help in QlikView

Logic Help

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

HI Guys ,

Need one simple help,

I have one table in which continuous dates are available ,

i want to pick max of date at backend it self

Table:

Load Date

from Path;

let vMaxDate = max(Date);

it is not giving output to me ...

and

i want to use this variable in other table;

load

(vMaxDate - DueDate) as Days

from Table;

How i can achieve this?

Re: Logic Help

swuehl — Sat, 09 Apr 2016 10:27:32 GMT

You can do it like

SET DateFormat = 'MM/DD/YYYY'; // Adapt to your Date date format

TMP:

Load Max(Date) as MaxDate

from Path;

Let vMaxDate = Peek('MaxDate',0,'TMP');

Re: Logic Help

pavan045 — Sat, 09 Apr 2016 10:42:32 GMT

Hi Abhay,

You need to use peek() function while assigning the max(Date) to your variable.

Try this,

Table1:

Load max(Date) as MaxDate resident <Your table name>;

LET vMaxDate = Peek('MaxDate', '0', 'Table1');

Hope this will help.

Thank you.

Re: Logic Help

sunny_talwar — Sat, 09 Apr 2016 14:56:49 GMT

Here is a sample sample to show how Stefan's idea will work:

Dates:

LOAD Date(MakeDate(2014, 12, 31) + RecNo()) as Date

AutoGenerate 300;

MaxDate:

LOAD Max(Date) as MaxDate

Resident Dates;

LET vMaxDate = Peek('MaxDate');

DROP Table MaxDate;

FactTable:

LOAD *,

$(vMaxDate) - DueDate as Days;

LOAD * Inline [

Dim, DueDate

A, 06/03/2015

B, 03/13/2015

C, 02/25/2015

];

Re: Logic Help

swuehl — Sat, 09 Apr 2016 15:47:53 GMT

And to explain it some more:

You can use aggregation functions like Max() only in a table LOAD.

Aggregation functions ‒ QlikView

You can then access the aggregated values, like the MaxDate in a variable assignment by the use of inter record functions like Peek()

Peek ‒ QlikView‌

If you then want to use the variable in another LOAD statement, you need to use dollar sign expansion of your variable:

Dollar-sign expansion using a variable ‒ QlikView