Discussion Board for collaboration on QlikView Scripting.
I need a help to create in a easy way period of datas for a price range table.
I'm using the solution Interval match with a calendar, but I would like to create it in the same table, today it is creating a $Syn Table.
I have the following data for example:
The results expected are the following:
I really appreciate if somebody can help me to do it smartelly.
Thanks in advance,
Go to Solution.
Yes, should be possible, maybe like this:
LOAD Date#(Datefrom,'MMM-YYYY') as Datefrom,
Date#(DataTo,'MMM-YYYY') as DataTo,
(html, codepage is 1252, embedded labels, table is @1);
LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month
(html, codepage is 1252, embedded labels, table is @2, filters(
Remove(Col, Pos(Top, 3))
inner join (CalendarWithProducts) IntervalMatch (Month) LOAD Datefrom, DataTo resident Values;
left join (CalendarWithProducts) LOAD * resident Values;
Please note that your Months need to have numerical representation, I think it would be best to use Dates like above formatted with Month name or year month as wanted.
You should be able to just inner join the two tables, thus removing the synthetic key.
Hope this helps,
I'm using the Inner Join, but it continue to appers.
Prices (is the table with the price range).
Month2 (is the table of Month available)
inner join (Month2)
Based on this script, it continue to create the $Syn Table
Yes, sorry, I intended to suggest an additional join, like
LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month ,
inner join (CalendarWithProducts) IntervalMatch (Month, Product) LOAD Datefrom, DataTo, Product resident Values;
drop fields Datefrom, DataTo;
drop table Values;
thank you very much!
You are helping a lot,
In my case the table the table CalendarWithProducts only has Dates (months to be precise).
Would be possible to do it without the Products?
I believe it would be simpler to use a while loop. See script and attached.
LOAD addmonths(date#(DateFrom,'MMM-YYYY'),iterno()-1) as Month,Product,ValueINLINE [DateFrom, DateTo, Product, ValueJan-2011, Mar-2011, a, 10 Feb-2011, Mar-2011, b, 20 Apr-2011, Jun-2011, a, 12 May-2011, Aug-2011, b, 19 ] WHILE addmonths(date#(DateFrom,'MMM-YYYY'),iterno()-1) <= date#(DateTo,'MMM-YYYY');