How to make a sub-group based on assumptions.

[mtucholski]

Hello,

I have a source table which looks like below (columns 1-6). But I need to add some data to it somehow (columns 7-9).

Here is some explanation:

I have an order (1) with plenty of documents (5) where 'Pw' stands for product made and 'Rw' for material used (4). In every order there is a sub order (9) which I'm aware of but there is no simple way to show it.

I know the start date (7) and the end date (8) of the sub order (9) because I know that after every group of documents marked as 'Pw' there will be at least one document marked as 'Rw' (4). So I know that the sub order starts with the first 'Pw' that occurs right after 'Rw' and it ends with the last 'Rw' that occurs right before another 'Pw'.

The question is how to do it in QlikView load script :-). I will apprecieate any help. Thank you.

1	2	3	4	5	6	7	8	9
order_no	date	time	document	document_no	material_code	from	to	sub_order_no
229185	2018-02-26	22:07:28	Pw	18638622	2495347	2018-02-26 22:07:28	2018-02-26 22:08:37	1
229185	2018-02-26	22:08:00	Rw	18638619	2478198	2018-02-26 22:07:28	2018-02-26 22:08:37	1
229185	2018-02-26	22:08:18	Rw	18638616	2494452	2018-02-26 22:07:28	2018-02-26 22:08:37	1
229185	2018-02-26	22:08:37	Rw	18638610	2493008	2018-02-26 22:07:28	2018-02-26 22:08:37	1
229185	2018-02-26	22:23:40	Pw	18638607	2495354	2018-02-26 22:23:40	2018-02-26 22:24:28	2
229185	2018-02-26	22:24:09	Rw	18638604	2493008	2018-02-26 22:23:40	2018-02-26 22:24:28	2
229185	2018-02-26	22:24:28	Rw	18638601	2494452	2018-02-26 22:23:40	2018-02-26 22:24:28	2
229185	2018-02-26	23:50:23	Pw	18638598	2495390	2018-02-26 23:50:23	2018-02-26 23:51:47	3
229185	2018-02-26	23:51:43	Pw	18638586	2494452	2018-02-26 23:50:23	2018-02-26 23:51:47	3
229185	2018-02-26	23:51:44	Rw	18638583	2493008	2018-02-26 23:50:23	2018-02-26 23:51:47	3
229185	2018-02-26	23:51:47	Rw	18638528	2494448	2018-02-26 23:50:23	2018-02-26 23:51:47	3
229185	2018-02-27	00:22:17	Pw	18638525	2495416	2018-02-27 00:22:17	2018-02-27 00:23:45	4
229185	2018-02-27	00:23:40	Rw	18638522	2494448	2018-02-27 00:22:17	2018-02-27 00:23:45	4
229185	2018-02-27	00:23:41	Rw	18638519	2493008	2018-02-27 00:22:17	2018-02-27 00:23:45	4
229185	2018-02-27	00:23:45	Rw	18638405	2462851	2018-02-27 00:22:17	2018-02-27 00:23:45	4
229185	2018-02-27	00:57:17	Pw	18638368	2495440	2018-02-27 00:57:17	2018-02-27 00:59:47	5
229185	2018-02-27	00:58:36	Pw	18638365	2493008	2018-02-27 00:57:17	2018-02-27 00:59:47	5
229185	2018-02-27	00:58:55	Pw	18638362	2489473	2018-02-27 00:57:17	2018-02-27 00:59:47	5
229185	2018-02-27	00:59:47	Rw	18638272	2462851	2018-02-27 00:57:17	2018-02-27 00:59:47	5

[mtucholski · Accepted solution]

Hi,

just add "and previous(document)<>document" in first "where" and we are almost there :-). So the final code is:

Submap:

Mapping Load 

order_no&'|'&date&'|'&time&'|'&document as key,

RowNo() as temp_sub_order_no

FROM 

[X]  (x)

where document='Pw' and previous(document)<>document

;

Table1:

Load

order_no,

date,

time,

document,

document_no,

material_code,

ApplyMap('Submap', key, null()) as sub_order_no

;

LOAD

order_no&'|'&date&'|'&time&'|'&document as key,

order_no,

date,

time,

document,

document_no,

material_code

FROM 

[X]  (x)

;

NoConcatenate

New:

Load

order_no,

date,

time,

document,

document_no,

material_code,

If( isnull(sub_order_no), peek(sub_order_no),sub_order_no )as sub_order_no

Resident Table1

order by date, time

;

Drop table Table1

;

There is some exceptions which I don't want to bring in here - just have to work it out by myself . Anyway, thank you for your help!