Discussion Board for collaboration related to Creating Analytics for QlikView.
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:
So the extended Table 1 (or a new table should look like this:
Are you aware of any way that this could be achieved?
Try this out:
(html, codepage is 1252, embedded labels, table is @1);
(html, codepage is 1252, embedded labels, table is @2);
LOAD Date(Min + IterNo() - 1) as NewDates
While Min + IterNo() - 1 <= Max;
LOAD Min(Dates) as Min,
Max(Dates) as Max
Left Join (Table)
LOAD Min(Dates) as NewDates,
NewDates as Date
Where Dates >= NewDates
Group By NewDates;
DROP Table Table2;
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);
Date(MinD + IterNo() -1) as Date
While MinD + IterNo() -1 <= MaxD;
(min(Dates)) as MinD,
(max(Dates)) as MaxD
Left Join (XMM)
load Dates as Date, Dates
if(len(trim(Dates)), Dates, Peek('Dates')) as Dates
order by Date desc;
DROP Table XMM;
DROP Table X;
LOAD Date(Date#(Date, 'DD-MMM-YYYY')) as Date,
Left Join (Y)
load Date, Dates as New_Date
DROP Table M;