topic Re: Lookup a value in another table in QlikView

Lookup a value in another table

Wed, 26 Oct 2016 14:04:09 GMT

All,

I am pretty new to Qlikview and I am working on a Dashboard.

I have two tables loaded in Qlikview.

Table1 has the following columns: Date, org, invoice_count

Table2 only has dates (but less dates than the dates in Table1)

I am looking for a function that extends bable1 by another date column and it should pick the next date that is available in Table2 if the exact value cannot be found.

Let's assume the tables look like this:

Table 1

Date	Organization	Count
21-OCT-2016	ABC	5
22-OCT-2016	ABC	10
23-OCT-2016	ABC	8
24-OCT-2016	ABC	5

Table 2

Dates
20-OCT-2016
21-OCT-2016
24-OCT-2016
25-OCT-2016

So the extended Table 1 (or a new table should look like this:

Date	Organization	Count	New_date
21-OCT-2016	ABC	5	21-OCT-2016
22-OCT-2016	ABC	10	24-OCT-2016
23-OCT-2016	ABC	8	24-OCT-2016
24-OCT-2016	ABC	5	24-OCT-2016

Are you aware of any way that this could be achieved?

Thanks,

Benny

Re: Lookup a value in another table

sunny_talwar — Wed, 26 Oct 2016 16:33:39 GMT

Try this out:

Table:

LOAD Date,

Organization,

Count

FROM

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

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

Table2:

LOAD Dates

FROM

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

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

Left Join(Table2)

LOAD Date(Min + IterNo() - 1) as NewDates

While Min + IterNo() - 1 <= Max;

LOAD Min(Dates) as Min,

Max(Dates) as Max

Resident Table2;

Left Join (Table)

LOAD Min(Dates) as NewDates,

NewDates as Date

Resident Table2

Where Dates >= NewDates

Group By NewDates;

DROP Table Table2;

Re: Lookup a value in another table

maxgro — Wed, 26 Oct 2016 16:41:39 GMT

LOAD Date(Date#(Dates, 'DD-MMM-YYYY')) as Dates, rowno() as Id

FROM [https://community.qlik.com/thread/237718] (html, codepage is 1252, embedded labels, table is @2);

XMM:

LOAD

Date(MinD + IterNo() -1) as Date

While MinD + IterNo() -1 <= MaxD;

LOAD

(min(Dates)) as MinD,

(max(Dates)) as MaxD

Resident X;

Left Join (XMM)

load Dates as Date, Dates

Resident X;

NoConcatenate load

Date,

if(len(trim(Dates)), Dates, Peek('Dates')) as Dates

Resident XMM

order by Date desc;

DROP Table XMM;

DROP Table X;

LOAD Date(Date#(Date, 'DD-MMM-YYYY')) as Date,

Organization,

Count

FROM

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

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

Left Join (Y)

load Date, Dates as New_Date

Resident M;

DROP Table M;