Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

inigoelv
Contributor

LOAD SCRIPT CONSUMPTION TABLE

I need the help of somebody.

I have a table of consumption like this:

ConsumptionSupplier
18/03/2018 14:01:22A
19/03/2018 22:22:05B
21/03/2018 0:40:14A
21/03/2018 0:40:45B
21/03/2018 9:56:38B
21/03/2018 11:13:18B
21/03/2018 22:19:34A
21/03/2018 22:21:34A
21/03/2018 22:23:25C
22/03/2018 2:13:10C
22/03/2018 2:13:51C
22/03/2018 2:15:57A
22/03/2018 3:08:11B
22/03/2018 7:23:29A
22/03/2018 14:12:04A
22/03/2018 21:59:13B
22/03/2018 22:01:11C

 

I need to group the table in a tabla of interval like this:

InitioFinSupplier
18/03/2018 14:01:2219/03/2018 22:22:05A
19/03/2018 22:22:0521/03/2018 0:40:14B
21/03/2018 0:40:1421/03/2018 0:40:45A
21/03/2018 0:40:4521/03/2018 22:19:34B
21/03/2018 22:19:3421/03/2018 22:23:25A
21/03/2018 22:23:2522/03/2018 2:15:57C
22/03/2018 2:15:5722/03/2018 3:08:11A
22/03/2018 3:08:1122/03/2018 7:23:29B
22/03/2018 7:23:2922/03/2018 21:59:13A
22/03/2018 21:59:1322/03/2018 22:01:11B
22/03/2018 22:01:11 C

 

Thanks in advance.

I attached the excell file too.

1 Solution

Accepted Solutions

Re: LOAD SCRIPT CONSUMPTION TABLE

Hi, this script cn create that table:

DataOrig:
LOAD Consumption,
Supplier
FROM
[.\Consumption.xlsx]
(ooxml, embedded labels, table is Hoja1);

tmpData:
LOAD Consumption as Initio,
If(Supplier=Peek(Supplier), Peek(Fin), Peek(Initio)) as Fin,
Supplier
Resident DataOrig order by Consumption desc;

FinalData:
NoConcatenate
LOAD *
Resident tmpData
Where Peek(Supplier)<>Supplier
Order by Initio;

DROP Table DataOrig;
DROP Table tmpData;

2 Replies

Re: LOAD SCRIPT CONSUMPTION TABLE

Hi, this script cn create that table:

DataOrig:
LOAD Consumption,
Supplier
FROM
[.\Consumption.xlsx]
(ooxml, embedded labels, table is Hoja1);

tmpData:
LOAD Consumption as Initio,
If(Supplier=Peek(Supplier), Peek(Fin), Peek(Initio)) as Fin,
Supplier
Resident DataOrig order by Consumption desc;

FinalData:
NoConcatenate
LOAD *
Resident tmpData
Where Peek(Supplier)<>Supplier
Order by Initio;

DROP Table DataOrig;
DROP Table tmpData;

Highlighted
inigoelv
Contributor

Re: LOAD SCRIPT CONSUMPTION TABLE

Many thanks.

It works perfectly.