Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data like below. I want all the main columns(Main1, Main2, Main3) to be merged into single column in qlikview and data should be in same format.
expected output
Please help!
Perhaps like this:
Data:
LOAD ID,
Main1,
Main2,
Main3,
Replace(
Replace(
Main1 & chr(10) & Main2 & chr(10) & Main3
, chr(10)&chr(10)
, chr(10)
)
, chr(10)&chr(10)
, chr(10)
) as Concatenated
FROM
c315886.xls
(biff, embedded labels, table is Sheet1$);
Perhaps like this:
Data:
LOAD ID,
Main1,
Main2,
Main3,
Replace(
Replace(
Main1 & chr(10) & Main2 & chr(10) & Main3
, chr(10)&chr(10)
, chr(10)
)
, chr(10)&chr(10)
, chr(10)
) as Concatenated
FROM
c315886.xls
(biff, embedded labels, table is Sheet1$);
Hi Suraj,
There are many ways to achieve the end result, please note that where I 'CREATE SOURCE DATA', you will use your source file;
//OPTION 1 - CROSSTABLE
/////////////////////////////////////////////////////////////
//CREATE SOURCE DATA
SOURCE:
LOAD * INLINE [
ID, Main1, Main2, Main3
1,AA,DR,ER
1,CC,AQ,YU
1,FF,NP,IK
1,KK
];
//FINAL_WITH_NULLS
FINAL_temp:
crosstable (SOURCE, FINAL, 1)
LOAD *
Resident SOURCE;
//FINAL_WITH_NULLS_REMOVED
FINAL:
NoConcatenate LOAD *
Resident FINAL_temp
WHERE NOT IsNull(FINAL);
DROP TABLES SOURCE, FINAL_temp;
DROP FIELD SOURCE FROM FINAL;
//////////////////////////////////////////////////////////////
//OPTION 2 - THREE RESIDENT LOADS
/////////////////////////////////////////////////////////////
//CREATE SOURCE DATA
SOURCE:
LOAD * INLINE [
ID, Main1, Main2, Main3
1,AA,DR,ER
1,CC,AQ,YU
1,FF,NP,IK
1,KK
];
//FINAL_WITH_NULLS_REMOVED
FINAL:
NoConcatenate LOAD
ID,
Main1 as FINAL
Resident SOURCE
WHERE LEN(Main1)>0;
Concatenate(FINAL)
LOAD
ID,
Main2 as FINAL
Resident SOURCE
WHERE LEN(Main2)>0;
Concatenate(FINAL)
LOAD
ID,
Main3 as FINAL
Resident SOURCE
WHERE LEN(Main3)>0;
DROP TABLE SOURCE;
//////////////////////////////////////////////////////////////
//Hope this helps!
It can be achieved through cross table load.
Refer this-->> The Crosstable Load
Thank you so much Gysbert.