Use of Subfield in QlikView Chart Dimension and Expression

[nav_pienaar]

Use of Subfield in QlikView Chart Dimension and Expression

I am sure I have done this before;

I need to bring the data into the application in a CONCAT format and then use subfield to “unzip” it in a chart Dimension and Expression. I do not want to use the subfield function in the Script.

Raw Data input must looks like this:

Unit	TYPE	VALUE
House	Large|Meduim|Small	1000|800|350
Car	Sport|Economic|Small	100|70|40
Office	Corp|Studio|Share	400|333|234

The Qlik Chart Output must look like this:

Unit	TYPE	VALUE
		3327
House	Large	1000
House	Meduim	800
House	Small	350
Car	Sport	100
Car	Economic	70
Car	Small	40
Office	Corp	400
Office	Studio	333
Office	Share	234

Any suggestions…?

Nav Pienaar

[Lisa_P]

Try this:

Source:
Load * inline [
Unit, TYPE, VALUE
House, Large|Meduim|Small, 1000|800|350
Car, Sport|Economic|Small, 100|70|40
Office, Corp|Studio|Share, 400|333|234];

[New]:
NoConcatenate
LOAD
[Unit],
Subfield([TYPE], '|', 1) AS [TYPE],
Subfield([VALUE], '|', 1) AS [VALUE]
Resident Source;

Concatenate(New)
LOAD
[Unit],
Subfield([TYPE], '|', 2) AS [TYPE],
Subfield([VALUE], '|', 2) AS [VALUE]
Resident Source;

Concatenate(New)
LOAD
[Unit],
Subfield([TYPE], '|', 3) AS [TYPE],
Subfield([VALUE], '|', 3) AS [VALUE]
Resident Source;

Drop table Source;

[Kushal_Chawda · Accepted solution]

 You need to handle this using valueloop function. 

// Create variable on front end using below expression which will be used to handle range of the loop

variable name : vLoop
expression: =max(total aggr(SubStringCount(TYPE,'|'),Unit))+1

// Create straight table 

Dimension:
Unit
=ValueLoop(1,$(vLoop))

Expressions:
1)TYPE
=if(isnull(SubField(TYPE,'|',ValueLoop(1,$(vLoop)))),0,SubField(TYPE,'|',ValueLoop(1,$(vLoop))))

2) VALUE
=rangesum(SubField(VALUE,'|',ValueLoop(1,$(vLoop))))

// Go to presentation tab and check suppress zero values.

[Kushal_Chawda]

You need to live with valueloop dimension  in chart. You cannot hide it. May be if you find a way then good. But having said that you can call it ID or something

[Sagar_Apte]

@Kushal_Chawda  This is interesting.   Thanks for sharing

[nav_pienaar]

Thanx @Kush  This is very helpful.