Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adinepstein
Contributor II
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
Master III
Master III

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

View solution in original post

8 Replies
shun_wong
Partner - Contributor III
Partner - Contributor III

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.

Anonymous
Not applicable

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
Contributor II
Contributor II
Author

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
Master III
Master III

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
Contributor II
Contributor II
Author

Hi Wallo,

Thank you for you quick answer.

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

Thanks

Adin

adinepstein
Contributor II
Contributor II
Author

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
Master III
Master III

Can You provide Your data ?

adinepstein
Contributor II
Contributor II
Author

I just solved the problem- instead of :


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


I did:

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


Thanks

Adin