loop through a table and for each row create new rows

[adinepstein]

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

[shun_wong]

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]

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]

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 · Accepted solution]

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]

Hi Wallo,

Thank you for you quick answer.

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

Thanks

Adin

[adinepstein]

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]

Can You provide Your data ?

[adinepstein]

I just solved the problem- instead of :

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

I did:

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

Thanks

Adin