topic Re: Variables in QlikView

Variables

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

I have below table loaded into qlikview. How can I Store MaxDate where ReportId='1.5' in a variable 'vMaxDate1.5' ?

ReportId	ReportName	MaxDate
1.5	xyz -1	7/25/2016
2.13	xyz -2	7/22/2016
2.14	xyz -3	7/22/2016
2.15	xyz -4	7/25/2016
2.16	xyz -5	7/25/2016
2.17	xyz -6	7/22/2016
2.18	xyz -7	7/25/2016

Re: Variables

Chanty4u — Tue, 26 Jul 2016 11:01:19 GMT

try below

tmpTable:

LOAD

ReportID,

Reportname

FROM ...

WHERE ReportId='1.5'

;

LET vMaxDate1.5= PEEK('ReportID',-1,'tmpTable')

DROP TABLE tmpTable;

Re: Variables

sunny_talwar — Tue, 26 Jul 2016 11:05:42 GMT

This is needed on the front end of the application or in the script?

Re: Variables

pooja_sn — Tue, 26 Jul 2016 11:06:33 GMT

In the script. The variable would to used to run a SQL query.

Re: Variables

sunny_talwar — Tue, 26 Jul 2016 11:11:46 GMT

May be like this (modifying Chanty's response)

tmpTable:

LOAD Max(MaxDate) as MaximumDate

Resident .....

Where ReportId = 1.5;

LET vMaxDate1.5 = PEEK('MaximumDate', -1, 'tmpTable')

DROP TABLE tmpTable;

Re: Variables

pooja_sn — Tue, 26 Jul 2016 11:21:38 GMT

This is what I have done currently, and I kind of want to enhance it.

The maxDate variable should not be calculated only for report 1.5 , but for all others too. And i'll be using these dates to write and SQL query and import data from database in incremental load.

Is there any way to load this tempTable only once and read maxDate for each reports ?

Re: Variables

sunny_talwar — Tue, 26 Jul 2016 11:30:56 GMT

How about this:

For i = 1 to FieldValueCount('ReportId')

LET vReport = FieldValue('ReportId', $(i));

tmpTable:

LOAD Max(MaxDate) as MaximumDate

Resident .....

Where ReportId = $(vReport);

LET vMaxDate$(vReport) = PEEK('MaximumDate', -1, 'tmpTable')

DROP TABLE tmpTable;

NEXT

Re: Variables

sunny_talwar — Tue, 26 Jul 2016 11:35:33 GMT

Or just this

Table:

LOAD ReportId,

ReportName,

MaxDate

FROM

[https://community.qlik.com/thread/226572]

(html, codepage is 1252, embedded labels, table is @1);

For i = 1 to FieldValueCount('ReportId')

LET vReport = FieldValue('ReportId', $(i));

LET vMaxDate$(vReport) = Peek('MaxDate', -$(i), 'Table');

NEXT