How to split one field in to multiple fields

[Lakshmanudui]

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

[vchuprina · Accepted solution]

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

[vchuprina]

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...

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

Regards,

Vitalii

[smith11484]

Note: You can also use the split option from the Data pane in the worksheet. In the Data pane, right-click the field you want to split, and then select Transform > Split. MyCoverageInfo.com