Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pooja_sn
Creator
Creator

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
MVP
MVP

try below

tmpTable:

LOAD

ReportID,

Reportname

FROM ...

WHERE ReportId='1.5'

;

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

DROP TABLE tmpTable;

sunny_talwar

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

pooja_sn
Creator
Creator
Author

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

sunny_talwar

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;

pooja_sn
Creator
Creator
Author

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 ?

sunny_talwar

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

sunny_talwar

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