Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Gurus,
I'm loading in QlikSense one table with Date-Time, Material Code and many other fields and KPI's. I'd like to add in this table two more fields called "Material Start" and "Material End" where Material Start would be the first material code in each Hour and Material End would be the first Material in the next Hour. I represent it with an example:
Date-Time | Material Code | Material Start | Material End |
---|---|---|---|
15th May 9:00:00 | AAA03 | AAA03 | AAA08 |
15th May 9:01:00 | AAA04 | AAA03 | AAA08 |
15th May 9:02:00 | AAA05 | AAA03 | AAA08 |
… | AAA03 | AAA08 | |
15th May 9:59:00 | AAA07 | AAA03 | AAA08 |
15th May 10:00:00 | AAA08 | AAA08 | AAA13 |
15th May 10:01:00 | AAA09 | AAA08 | AAA13 |
15th May 10:02:00 | AAA10 | AAA08 | AAA13 |
… | AAA08 | AAA13 | |
15th May 10:59:00 | AAA12 | AAA08 | AAA13 |
15th May 11:00:00 | AAA13 | AAA13 | AAA19 |
15th May 11:01:00 | AAA14 | AAA13 | AAA19 |
15th May 11:02:00 | AAA15 | AAA13 | AAA19 |
… | AAA13 | AAA19 | |
15th May 11:58:00 | AAA17 | AAA13 | AAA19 |
15th May 11:59:00 | AAA18 | AAA13 | AAA19 |
15th May 12:00:00 | AAA19 | AAA19 | ... |
15th May 12:01:00 | AAA20 | AAA19 | ... |
… | ... | ||
… | ... | ||
15th May 23:59:00 | AAA50 | ... | ... |
16th May 0:00:00 | AAA51 | AAA51 | AAA54 |
16th May 0:01:00 | AAA52 | AAA51 | AAA54 |
16th May 0:59:00 | AAA53 | AAA51 | AAA54 |
16th May 1:00:00 | AAA54 | AAA54 | AAA54 |
Could anyone explain how it would be possible to add this two fields in the script?
Thanks in advance!!
Regards,
Jordi
May be like this
Table:
LOAD DateTime,
Material,
Date(Floor(DateTime, 1/24)) as DateandHour
FROM ....;
Left Join (Table)
LOAD DateandHour,
FirstSortedValue(Material, DateTime) as MaterialStart,
FirstSortedValue(Material, -DateTime) as MaterialEnd
Resident Table
Group By DateandHour;
is the Date-Time format is same in actual data?
Date Format is like:
20/05/2017 09:00:00
Thanks,
Regards,
Jordi
Data:
LOAD *,
if(Minute(DateTime)>=1,1,0) as HourFlag;
LOAD replace(Replace([Date-Time],'15th May','15-May-2017'),'16th May','16-May-2017') as DateTime,
[Material Code]
FROM
[https://community.qlik.com/thread/276256]
(html, codepage is 1252, embedded labels, table is @1, filters(
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 9)),
Remove(Row, Pos(Top, 13)),
Remove(Row, Pos(Top, 17)),
Remove(Row, Pos(Top, 17))
));
New:
NoConcatenate
LOAD *,
if(HourFlag=1 and Previous(HourFlag)=0,Peek([Material Code]),if(HourFlag=0,[Material Code], Peek([Material Code new]))) as [Material Code new]
Resident Data
Order by DateTime,[Material Code];
DROP Table Data;
DROP Field HourFlag;
Note: I have done some transformation (highlighted in bold) on DateTime field to make that in proper format, in your data if DateTime is already in proper format then no need to perform this