Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

pooja_sn
Contributor

Variables

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

ReportIdReportNameMaxDate
1.5xyz -17/25/2016
2.13xyz -27/22/2016
2.14xyz -37/22/2016
2.15xyz -47/25/2016
2.16xyz -57/25/2016
2.17xyz -67/22/2016
2.18xyz -77/25/2016
7 Replies
Chanty4u
Esteemed Contributor III

Re: Variables

try below

tmpTable:

LOAD

ReportID,

Reportname

FROM ...

WHERE ReportId='1.5'

;

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

DROP TABLE tmpTable;

Re: Variables

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

pooja_sn
Contributor

Re: Variables

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

Re: Variables

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;

Highlighted
pooja_sn
Contributor

Re: Variables

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

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

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


Capture.PNG