Splitting a value into multiple columns

[Ashti]

Hi,

I have a column with multiple delimiters as shown below

Col 1     Col2

1             [{ "Sl_id" : "1235", "prod_name" : "Clothes"}],

               [{ "Sl_id" : "1234", "prod_name" : "Accessroies"}]

2           [{ "Sl_id" : "1234", "prod_name" : "Accessroies"}]

Expected Output :

Col 1  Prod_id  Prod_name

1         1235        Clothes

1          1234       Accessories

2          1234       Accessories

Whats the right way to achieve this...Subfield is not giving me the exact results...

its resulting in multiple rows instead of columns

[Taoufiq_Zarra]

@Ashti  you can use json parser

but if you must work with file like sample you can

suppose you have this excel file as input :

in load script you can use this :

Data:

load [Col 1],
	 purgechar(subfield(CC,':',1),'"{[]}') as Prod_id,
	 purgechar(subfield(CC,':',2),'"{[]}') as Prod_name;

LOAD 
	 [Col 1],
	 [Col 2],
     subfield(replace(replace([Col 2],'"Sl_id" :',''),', "prod_name"',''),',') as CC
     
FROM
[C:\Users\Lenovo thinkpad T480\Downloads\Sans nom 1.xls]
(biff, embedded labels, table is Feuille1$);

the output :

[pedrobergo]

Hi @Ashti 

It seems that each record contains a JSON info with many other records inside that, so you´ll need to create a table with this records associated with main table combining SubField and JsonGet.

SplittedTable:
Load Col1,
	 PurgeChar(JsonGet(JsonField,'/Sl_id'),'"') as Sl_id,
     PurgeChar(JsonGet(JsonField,'/prod_name'),'"') as prod_name;
Load Col1,
	 PurgeChar(JsonField,'[]') AS JsonField;
Load Col1, SubField(Col2,'],[') AS JsonField
Resident Table;

You cannot use JsonGet directly because each json record doesn´t contains a path level or record identifier, so it is needed to separate fields using Subfield, remove brackets (PurgeChar) then get fields from Json (JsonGet). Need also to remove the quotes remaining on fields (Purgechar again).

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFuncti...

[],

Pedro