Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Use of Subfield in QlikView Chart Dimension and Expression

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

 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.

 

 

 

View solution in original post

5 Replies
Highlighted
Employee
Employee

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;

Highlighted
MVP
MVP

 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.

 

 

 

View solution in original post

Highlighted
MVP
MVP

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

Highlighted
Contributor III
Contributor III

@Kush  This is interesting.   Thanks for sharing

Highlighted
Contributor II
Contributor II

Thanx @Kush  This is very helpful.