Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

adinepstein
New Contributor II

loop through a table and for each row create new rows

Hi,

My raw data is the following:

  

Key[CHART] Time in Status
BT-19691_*:*_1_*:*_79965597_*|*_6_*:*_1_*:*_0
BT-19601_*:*_2_*:*_1319584_*|*_6_*:*_1_*:*_0
BT-19551_*:*_1_*:*_1080197_*|*_6_*:*_1_*:*_0_*|*_10052_*:*_1_*:*_357672353
BT-19361_*:*_1_*:*_66272530_*|*_6_*:*_1_*:*_0
BT-19341_*:*_1_*:*_94463824_*|*_6_*:*_1_*:*_0
BT-19321_*:*_1_*:*_248563_*|*_6_*:*_1_*:*_0
BT-19141_*:*_1_*:*_690337206_*|*_6_*:*_1_*:*_0
BT-19111_*:*_1_*:*_517796_*|*_6_*:*_1_*:*_0
BT-19031_*:*_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):

   

Keystatusstatetime
BT-19691179965597
BT-1969610
BT-1960121319584
BT-1960610
BT-1955111080197
BT-1955610
BT-195511357672353

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

thank you

Adin

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

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

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

8 Replies
Highlighted
Partner
Partner

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

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.

atkinsow
Valued Contributor II

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

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?

adinepstein
New Contributor II

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

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

antoniotiman
Honored Contributor III

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

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

adinepstein
New Contributor II

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

Hi Wallo,

Thank you for you quick answer.

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

Thanks

Adin

adinepstein
New Contributor II

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

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

antoniotiman
Honored Contributor III

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

Can You provide Your data ?

adinepstein
New Contributor II

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

I just solved the problem- instead of :


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


I did:

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


Thanks

Adin