topic parsing multiple values from a column and updating to another another column in App Development

parsing multiple values from a column and updating to another another column

tmahmood — Sat, 16 Nov 2024 18:09:38 GMT

Hello Everyone,

I have a table load that has one column with multiple values separated by #, that I want to update with another column. In my example below I have different values in column D that I need to parse and then update Column B

Here what I have in my file

A	B	D
DATA		01#06#AX#BA
DATA		01#06#AX#BA
DATA		01#06#AX#BA
DATA		01#06#AX#BA
XYZ		02#03#04
XYZ		02#03#04
XYZ		02#03#04

Here is I want to achieve

A	B	D
DATA	01	01#06#AX#BA
DATA	06	01#06#AX#BA
DATA	AX	01#06#AX#BA
DATA	BA	01#06#AX#BA
XYZ	02	02#03#04
XYZ	03	02#03#04
XYZ	04	02#03#04

Please help, thank you.

Re: parsing multiple values from a column and updating to another another column

Taoufiq_Zarra — Wed, 02 Sep 2020 20:54:17 GMT

Hi @tmahmood

One solution:

Input: LOAD * INLINE [ A, B, D DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA XYZ,,02#03#04 XYZ,,02#03#04 XYZ,,02#03#04 ]; Tmp: noconcatenate load * resident Input order by A; drop table Input; T: noconcatenate load *,if(rowno()=1,1,if(peek(A)=A,peek(n)+1,1)) as n resident Tmp; drop table Tmp; Final: noconcatenate load A,TextBetween('#'&D&'#','#','#',n) as B,D resident T; drop table T;

output:

Re: parsing multiple values from a column and updating to another another column

Kushal_Chawda — Wed, 02 Sep 2020 21:36:55 GMT

@tmahmood try below

Data: LOAD * INLINE [ A, B, D DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA XYZ,,02#03#04 XYZ,,02#03#04 XYZ,,02#03#04 ]; T1: LOAD A, D, AutoNumber(RowNo(),D) as Key Resident Data Order by A,D; DROP Table Data; Final: LOAD *, SubField(D,'#',Key) as B Resident T1; DROP Table T1;

Re: parsing multiple values from a column and updating to another another column

tmahmood — Thu, 03 Sep 2020 00:28:40 GMT

@Taoufiq_Zarra ,

Thank you so much for solution. I have a table of almost over 15000 rows and that could add with the period of time. Also column D could have any number of values ranging from 2 to 15. How do I incorporate these and make it more dynamic than hard coded.

Regrads,

Tallat

Re: parsing multiple values from a column and updating to another another column

tmahmood — Wed, 02 Sep 2020 21:48:38 GMT

Hi Kush,

Regrads,

Tallat

Re: parsing multiple values from a column and updating to another another column

Kushal_Chawda — Wed, 02 Sep 2020 21:50:51 GMT

@tmahmood did you try what I suggested?

Re: parsing multiple values from a column and updating to another another column

Kushal_Chawda — Wed, 02 Sep 2020 21:53:47 GMT

@tmahmood what is hardcoded in my solution? Would you share some sample data based on your actual data columns?

Re: parsing multiple values from a column and updating to another another column

tmahmood — Thu, 03 Sep 2020 00:28:03 GMT

@Kushal_Chawda

Here is the sample of data - I have about over 15000 rows like these with column Type Combination ranging from 1 to 15.

I havent tried your solution yet as I see below as Hardcoded, I am new to QlikSense so please forgive me for being so naïve and ignorant . Thank you

DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA XYZ,,02#03#04 XYZ,,02#03#04 XYZ,,02#03#04

Customer	ID	Consolidated Type	Type Combination
SADD			AX#BA
SADD			AX#BA
AEA			AX#BA
AEA			AX#BA
SAKD			FN#FX
SAKD			FN#FX
RAKE			71#AZ
RAKE			71#AZ
JAND			AX#BA
JAND			AX#BA
PANT			FN#FX
PANT			FN#FX
DEARN			FN#FX
DEARN			FN#FX
ARND			FN#FX
ARND			FN#FX
SBAPT			FN#FX
SBAPT			FN#FX
BAGI			75#AX
BAGI			75#AX
BCTT			FN#FX
BCTT			FN#FX
MBEER			FN#FX
MBEER			FN#FX
DATA			01#06#AX#BA
DATA			01#06#AX#BA
DATA			01#06#AX#BA
DATA			01#06#AX#BA
XYZ			02#03#04
XYZ			02#03#04
XYZ			02#03#04
ABC			01#06#AX#BA#FN#FX
ABC			01#06#AX#BA#FN#FX
ABC			01#06#AX#BA#FN#FX
ABC			01#06#AX#BA#FN#FX
ABC			01#06#AX#BA#FN#FX
ABC			01#06#AX#BA#FN#FX

Re: parsing multiple values from a column and updating to another another column

Taoufiq_Zarra — Thu, 03 Sep 2020 07:52:15 GMT

@tmahmood

in a solution I proposed to you, there is no hard code
just change this part of the code

LOAD * INLINE [ A, B, D DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA DATA,,01#06#AX#BA XYZ,,02#03#04 XYZ,,02#03#04 XYZ,,02#03#04 ];

by your Data source: From database, Excel,....

for example in the sample you sent :

Input: LOAD * INLINE [ A, B, E, D SADD, , , AX#BA SADD, , , AX#BA AEA, , , AX#BA AEA, , , AX#BA SAKD, , , FN#FX SAKD, , , FN#FX RAKE, , , 71#AZ RAKE, , , 71#AZ JAND, , , AX#BA JAND, , , AX#BA PANT, , , FN#FX PANT, , , FN#FX DEARN, , , FN#FX DEARN, , , FN#FX ARND, , , FN#FX ARND, , , FN#FX SBAPT, , , FN#FX SBAPT, , , FN#FX BAGI, , , 75#AX BAGI, , , 75#AX BCTT, , , FN#FX BCTT, , , FN#FX MBEER, , , FN#FX MBEER, , , FN#FX DATA, , , 01#06#AX#BA DATA, , , 01#06#AX#BA DATA, , , 01#06#AX#BA DATA, , , 01#06#AX#BA XYZ, , , 02#03#04 XYZ, , , 02#03#04 XYZ, , , 02#03#04 ABC, , , 01#06#AX#BA#FN#FX ABC, , , 01#06#AX#BA#FN#FX ABC, , , 01#06#AX#BA#FN#FX ABC, , , 01#06#AX#BA#FN#FX ABC, , , 01#06#AX#BA#FN#FX ABC, , , 01#06#AX#BA#FN#FX ]; Tmp: noconcatenate load * resident Input order by A; drop table Input; T: noconcatenate load *,if(rowno()=1,1,if(peek(A)=A,peek(n)+1,1)) as n resident Tmp; drop table Tmp; Final: noconcatenate load A,TextBetween('#'&D&'#','#','#',n) as B,D resident T; drop table T;

output:

Re: parsing multiple values from a column and updating to another another column

Kushal_Chawda — Thu, 03 Sep 2020 08:55:42 GMT

@tmahmood Just try the solution I suggested. There is no hardcode it will work fine on your data. Just replace the inline table with your actual data and name of field with actual field name