topic How to split one field in to multiple fields in QlikView

How to split one field in to multiple fields

Lakshmanudui — Fri, 22 Apr 2022 05:03:11 GMT

Hi All,

I have data like below format

Code	Level1_Region_Sales	Level1_Region_Profit	Level1_Region_Quantity	Level1_Zone_Sales	Level1_Zone_Profit	Level1_Zone_Quantity
1001	100	250	4	250	100	5

Data having up to level6 like

Level2_Region_Sales, Level2_Region_Profit, Level2_Region_Quantity, Level2_Zone_Sales, Level2_Zone_Profit like that

I want to show data in below Format in Pivot table.

Code	Level1
	Region			Zone
	Sales	Profit	Quantity	Sales	Profit	Quantity
1001	100	250	4	250	100	5

up to data showing Level6

Can you please suggest me how to convert data in to Script side in Qlikview.

Can you please share any sample example.

Thanks & Regards,

Lakshman

Re: How to split one field in to multiple fields

vchuprina — Fri, 22 Apr 2022 13:37:55 GMT

Hi Lakshman,

Try script below:

TMP:
CrossTable(ColumnName, Value, 1)
LOAD * Inline[
Code, Level1_Region_Sales, Level1_Region_Profit, Level1_Region_Quantity, Level1_Zone_Sales, Level1_Zone_Profit, Level1_Zone_Quantity
1001, 100, 250, 4, 250, 100, 5
];

DATA:
LOAD
     Code,
     ColumnName,
     Value,
     SubField(ColumnName, '_', 1) AS Level,
     SubField(ColumnName, '_', 2) AS RegionZone,
     SubField(ColumnName, '_', -1) AS Measure
Resident TMP;

DROP Table TMP;

Dimensions:

Expression

Regards,

Vitalii

Re: How to split one field in to multiple fields

vchuprina — Fri, 22 Apr 2022 13:39:04 GMT

For such cases you should use Crosstable and subfield function

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/crosstable.htm

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions/SubField.htm

Regards,

Vitalii