topic Re: loop through a table and for each row create new rows in QlikView

loop through a table and for each row create new rows

adinepstein — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

My raw data is the following:

Key	[CHART] Time in Status
BT-1969	1_:_1_:_79965597_\|_6_:_1_:_0
BT-1960	1_:_2_:_1319584_\|_6_:_1_:_0
BT-1955	1_:_1_:_1080197_\|_6_:_1_:_0_\|_10052_:_1_:_357672353
BT-1936	1_:_1_:_66272530_\|_6_:_1_:_0
BT-1934	1_:_1_:_94463824_\|_6_:_1_:_0
BT-1932	1_:_1_:_248563_\|_6_:_1_:_0
BT-1914	1_:_1_:_690337206_\|_6_:_1_:_0
BT-1911	1_:_1_:_517796_\|_6_:_1_:_0
BT-1903	1_:_1_:_73098196_\|_3_:_1_:_109704_\|_10053_:_1_:_11183820_\|_11187_:_1_:_0

I'm trying to create a new table that will look like the following(only the first 3 rows):

Key	status	state	time
BT-1969	1	1	79965597
BT-1969	6	1	0
BT-1960	1	2	1319584
BT-1960	6	1	0
BT-1955	1	1	1080197
BT-1955	6	1	0
BT-1955	1	1	357672353

It must be dynamic because for each row the number of new rows can change, according to the number of separators( *|* )

thank you

Adin

Re: loop through a table and for each row create new rows

-SW- — Thu, 13 Jul 2017 15:27:49 GMT

Hi Adin,

Please look at this thread. It should do what you need it to, with a different delimitter:

How to separate comma delimited field during load

Obviously you will have to limit it to 3 for your rows.

Re: loop through a table and for each row create new rows

Anonymous — Thu, 13 Jul 2017 15:30:53 GMT

This will create your rows.

MakeRows:

Load

Key,

subfield('[CHART] Time in Status','*|*') as NewRows

From YourTable;

Do you need the code for the column breakout too?

Re: loop through a table and for each row create new rows

adinepstein — Thu, 13 Jul 2017 15:42:01 GMT

Hi Shun,

Thanks for your quick answer.

After I separate each row according to the delimiter, i want to create 3 new columns Status, state and time (1_*:*_1_*:*_79965597_).

How can I do that in the same while?

Thanks

Adin

Re: loop through a table and for each row create new rows

antoniotiman — Thu, 13 Jul 2017 15:44:18 GMT

Hi Adin,

LOAD Key,
SubField(NewText,':',1) as status,
SubField(NewText,':',2) as state,
SubField(NewText,':',-1) as time;
LOAD Key,PurgeChar(SubField(Text,'|'),'_*') as NewText
Inline [
Key Text
BT-1969 1_*:*_1_*:*_79965597_*|*_6_*:*_1_*:*_0
BT-1960 1_*:*_2_*:*_1319584_*|*_6_*:*_1_*:*_0
BT-1955 1_*:*_1_*:*_1080197_*|*_6_*:*_1_*:*_0_*|*_10052_*:*_1_*:*_357672353
BT-1936 1_*:*_1_*:*_66272530_*|*_6_*:*_1_*:*_0
BT-1934 1_*:*_1_*:*_94463824_*|*_6_*:*_1_*:*_0
BT-1932 1_*:*_1_*:*_248563_*|*_6_*:*_1_*:*_0
BT-1914 1_*:*_1_*:*_690337206_*|*_6_*:*_1_*:*_0
BT-1911 1_*:*_1_*:*_517796_*|*_6_*:*_1_*:*_0
BT-1903 1_*:*_1_*:*_73098196_*|*_3_*:*_1_*:*_109704_*|*_10053_*:*_1_*:*_11183820_*|*_11187_*:*_1_*:*_0
](delimiter is spaces);

Regards,

Antonio

Re: loop through a table and for each row create new rows

adinepstein — Thu, 13 Jul 2017 15:44:27 GMT

Hi Wallo,

Thank you for you quick answer.

Can you please give me also the code for the column breakout?

Thanks

Adin

Re: loop through a table and for each row create new rows

adinepstein — Tue, 18 Jul 2017 07:47:24 GMT

Hi Antonio,

My data contains 7000 rows, when I run your script on the data is gives me an out of memory error.

I tried to split the subFields to different tables but it doesn't help.

Do you have an idea how to solve the problem?

Thank you

Adin

Re: loop through a table and for each row create new rows

antoniotiman — Tue, 18 Jul 2017 07:54:07 GMT

Can You provide Your data ?

Re: loop through a table and for each row create new rows

adinepstein — Tue, 18 Jul 2017 08:12:34 GMT

I just solved the problem- instead of :

SubField(NewText,':',-1) as time;

I did:

SubField(NewText,':',3) as time;

Thanks

Adin