Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
@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;
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
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
@tmahmood did you try what I suggested?
@tmahmood what is hardcoded in my solution? Would you share some sample data based on your actual data columns?
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 |
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:
@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