topic Re: Calculate Material in QlikView

Calculate Material

jorditorras — Thu, 28 Sep 2017 11:34:01 GMT

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

Re: Calculate Material

sunny_talwar — Thu, 28 Sep 2017 12:01:00 GMT

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;

Re: Calculate Material

Kushal_Chawda — Thu, 28 Sep 2017 12:04:17 GMT

is the Date-Time format is same in actual data?

Re: Calculate Material

jorditorras — Thu, 28 Sep 2017 12:17:30 GMT

Date Format is like:

20/05/2017 09:00:00

Thanks,

Regards,

Jordi

Re: Calculate Material

Kushal_Chawda — Thu, 28 Sep 2017 12:46:57 GMT

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