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

[tmahmood]

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.

[Taoufiq_Zarra]

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:

[Kushal_Chawda]

@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;

[tmahmood]

@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

[tmahmood]

Hi Kush,

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

[Kushal_Chawda]

@tmahmood  did you try what I suggested?

[Kushal_Chawda]

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

[tmahmood]

@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

[Taoufiq_Zarra]

@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:

[Kushal_Chawda]

@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