Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Colleagues,
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:
Datefrom | DataTo | Product | value |
---|---|---|---|
Jan-2011 | Mar-2011 | a | 10 |
Feb-2011 | Mar-2011 | b | 20 |
Apr-2011 | Jun-2011 | a | 12 |
May-2011 | Aug-2011 | b | 19 |
The results expected are the following:
Month | Product | Value |
Jan-11 | a | 10 |
Feb-11 | a | 10 |
Mar-11 | a | 10 |
Apr-11 | a | 12 |
May-11 | a | 12 |
Jun-11 | a | 12 |
Feb-11 | b | 20 |
Mar-11 | b | 20 |
Apr-11 | b | 20 |
May-11 | b | 19 |
Jun-11 | b | 19 |
Jul-11 | b | 19 |
Aug-11 | b | 19 |
I really appreciate if somebody can help me to do it smartelly.
Thanks in advance,
Leandro Duarte
Yes, should be possible, maybe like this:
Values:
LOAD Date#(Datefrom,'MMM-YYYY') as Datefrom,
Date#(DataTo,'MMM-YYYY') as DataTo,
Product,
value
FROM
[http://community.qlik.com/thread/38834?tstart=0]
(html, codepage is 1252, embedded labels, table is @1);
CalendarWithProducts:
LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month
// ,Product
FROM
[http://community.qlik.com/thread/38834?tstart=0]
(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.
Regards,
Stefan
You should be able to just inner join the two tables, thus removing the synthetic key.
Hope this helps,
Stefan
Hi Stefan,
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)
INTERVALMATCH (Date)
LOAD
Datefrom,
Dateto
RESIDENT Prices;
/////
Based on this script, it continue to create the $Syn Table
Yes, sorry, I intended to suggest an additional join, like
Values:
LOAD Date#(Datefrom,'MMM-YYYY') as Datefrom,
Date#(DataTo,'MMM-YYYY') as DataTo,
Product,
value
FROM
[http://community.qlik.com/thread/38834?tstart=0]
(html, codepage is 1252, embedded labels, table is @1);
CalendarWithProducts:
LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month ,
Product
FROM
[http://community.qlik.com/thread/38834?tstart=0]
(html, codepage is 1252, embedded labels, table is @2, filters(
Remove(Col, Pos(Top, 3))
));
inner join (CalendarWithProducts) IntervalMatch (Month, Product) LOAD Datefrom, DataTo, Product resident Values;
left join (CalendarWithProducts) LOAD * resident Values;
drop fields Datefrom, DataTo;
drop table Values;
Hope this helps,
Stefan
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?
Thanks in advance,
Yes, should be possible, maybe like this:
Values:
LOAD Date#(Datefrom,'MMM-YYYY') as Datefrom,
Date#(DataTo,'MMM-YYYY') as DataTo,
Product,
value
FROM
[http://community.qlik.com/thread/38834?tstart=0]
(html, codepage is 1252, embedded labels, table is @1);
CalendarWithProducts:
LOAD Date(Date#(Month,'MMM-YY'),'MMM-YYYY') as Month
// ,Product
FROM
[http://community.qlik.com/thread/38834?tstart=0]
(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.
Regards,
Stefan
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
,Value
INLINE [
DateFrom, DateTo, Product, Value
Jan-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');