Extract Json field from csv file

[Pico]

Hi there, I have a csv file with 3 columns:

YearMonth	Project	Distribution
202412	it-secu-01	{"Company1":10,"Company2":30,Company3":50,Company4":20}
202412	de-jkns-04	{"Company1":10,"Company2":10,Company3":0,Company4":80}

where "Company1":10 means we have to extract the value 10(%) and associated to Company1.

the expected result would be:

YearMonth	Project	Company	%
202412	it-secu-01	Company1	10
202412	it-secu-01	Company2	30
202412	it-secu-01	Company3	50
202412	it-secu-01	Company4	20
202412	de-jkns-04	Copmany1	10
202412	de-jkns-04	..	...

Right now i have

Table:

LOAD
month, 

projectId, 

cost_percentage;

[total_cost_distribution_table]:

SELECT `month`,

projectId,

`cost_percentage`

FROM [DB Object].

How can I achieve this without getting crazy with "MID" "LEFT" adn "RIGHT" functions?

Note: I read about the function "JsonGET" gut it seems useless to me. Correct me if I'm wrong

Thank you

[QFabian · Accepted solution]

Hi, please check this example : 

This is the example script :

RawData:

Load * INLINE [

YearMonth, Project, Distribution

202412, it-secu-01, "{"Company1":10,"Company2":30,"Company3":50,"Company4":20}"

202412, de-jkns-04, "{"Company1":10,"Company2":10,"Company3":0,"Company4":80}"

];

Data:

Load 

YearMonth,

Project,

    Distribution,

    subfield(purgechar(Distribution, '{}"'), ',') as Distribution_SemiProcessed

Resident RawData;

drop table RawData;

Data2:

Load 

YearMonth,

Project,

   Distribution,

   subfield(Distribution_SemiProcessed, ':', 1) as Company,

   subfield(Distribution_SemiProcessed, ':', 2) as [%]

Resident Data;

drop table Data;

exit script;

[QFabian]

Check Subfield function in Qlik Help:

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/StringF...

@Pico :

Qlik Help :

🤣

[QFabian]

notice that i had to put a couple of double quoetes for the exercise :