Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Lookup a value in another table

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


DateOrganizationCount
21-OCT-2016ABC5
22-OCT-2016ABC10
23-OCT-2016ABC8
24-OCT-2016ABC5



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:

DateOrganizationCountNew_date
21-OCT-2016ABC521-OCT-2016
22-OCT-2016ABC1024-OCT-2016
23-OCT-2016ABC824-OCT-2016
24-OCT-2016ABC524-OCT-2016

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

Thanks,

Benny




2 Replies

Re: Lookup a value in another table

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;

Capture.PNG

MVP
MVP

Re: Lookup a value in another table

1.png

X:

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;

M:

NoConcatenate load

  Date,

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

Resident XMM

order by Date desc;

DROP Table XMM;

DROP Table X;

Y:

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;

Community Browser