Splitting one row into multiple depending on contents of one field...

[#1]

Hi all,

I am reading some historical information into QlikView, where each change made to a sales opportunity in our Maximizer database has a corresponding entry in a 'Notes' table.

However I have noticed that sometimes multiple changes are stored in ONE row - where I thought each change was a separate entry.

Is there a way I can read the Notes table in, then cycle through each row, analysing the 'TextCol' field and splitting it based on the presence of a particular string?

For example, one entry contains the string (in the textcol field):

"Vincent Stefanovic updated the following fields:
Added user-defined field 'Conversion Rate to £' with the value '1.45'
Added user-defined field 'License Value - £' with the value '400,000'
Added user-defined field 'Local Currency' with the value 'Euros'
Added user-defined field 'Weighted Licence Value - £' with the value '0'"

I need to split this into 4 distinct rows (one for each 'Added user-defined field*'

Any help will be appreciated.

Thanks in advance,

Matt

[#2]

Hi all,

can anyone suggest any means of doing this.

I think I may be able to cycle through each row and look at the one field, but how do I then write each row BACK to a new table?

Thanks in advance,

Matt

[#3 · Accepted solution]

Hi Matt,

here are a quick shot.

Good luck!

Rainer

[#4]

Hi Rainer,

I think I love you!

I was able to use your example with a little adjustment and have it loading the individual textfields into a single field and concatenating each load into one big table with a single row for each of the values.

perfect.

I'm slowly beginning to use more and more of the inbuilt functionality within QlikView script - something I have not really needed to do in the past.

Thank you for helping me understand those functions.

Many thanks,

Matt

[#5]

Thank you Matt!

I´m glad if I can help.

[#6]

actually,

I am having a slight issue with this.

I need to apply it to read in a table full of records (each record containing the textfield that may contain more than one value)

My loop through each record is not working properly, I don't think.

I need to look at each rows 'textfield' column and then count how many occurances of the string exist.
then for each one of those I want to write the whole row again with the additional column of Field (containing the occurance of the string )

I'm not sure the main loop through the records is working, as in debug my variable vHowMany is NULL after the first record.

TextFields:
LOAD
TextField,
Type,
SubStringCount(TextField, 'Added user-defined field ') as HowMany

FROM
Matt.xlsx
(ooxml, embedded labels, table is Sheet1);

FOR RecordNumber = 1 TO (NoOfRows('TextFields'))
Let vHowMany = Peek('HowMany',0, 'Temp_TextField');
For vI=0 to $(vHowMany)+1
Fields:
Load
TextField,
Type,
SubField(TextField, 'user-defined field ', $(vI)) as Field
Resident TextFields;
//Drop Table Fields;
Next
Next
drop table TextFields

any ideas where I am failing?

Regards,

Matt

[#7]

Good morning Matt,

please upload some data like your Matt.xlsx file. Than I can have a closer look to it.

Rainer

[#8]

Hi Rainer,
thanks for the response. Have uploaded Matt.xlsx

This now contains 6 data rows. Have added another field called Type just so I can check that the type number is put into every new row for that original row (if that makes sense)

Some rows have 3 occurances of 'user-defined field' some have 2 one has 1 - so there is a mixture.

so I really need to be able to cycle through each row, then cycle through each occurance of 'user-defined field' in TextField and LOAD a new row into QlikView for each one - maintaining other fields (such as Type, in my example) and then drop the original table afterwards.

Thanks for all of your help with this.

Matt

[#9]

Hi Matt,

here are a new version.

Hope you are now able to solve your business problem.

Good luck!

Rainer