Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
sanp96
Contributor III
Contributor III

Getting values based on a string match in another column of chart type table

Hi all,

I am new to Qlik and have been working on a table visualization. Unfortunately, I cannot share the actual data and hence I am using examples like 'abc''xyz'...I have a column in my table chart, where every row contains either the string 'discount' or the string 'Policy' along with other words. I have another column (amount) which contains the corresponding values for these. 

It looks something like this....Again, the chart type is Table

TypeAmount
abc discount30
xyz policy100
abc discount80
xyz policy50

 

I want to create two separate columns  'Discount Amount' and 'Policy Amount' which will get the values from 'Amount' depending on whether the column contains 'Discount' or 'Policy' ad have the column totals at the top.

What I'm trying to get is as follows:

TypeDiscount amountPolicy Amount
 110150
abc discount30-
xyz policy-100
abc discount80-
xyz policy-50

 

Any help with this is highly appreciated. 

Thanks!

Labels (6)
1 Solution

Accepted Solutions
jwjackso
Specialist II
Specialist II

With data

Data:
Load * Inline [
Type,Amount
abc discount,30
xyz policy,100
abc discount,80
xyz policy,50
];

NewData:
Load Type,
Amount as [Discount Amount],
'' as [Policy Amount]
Resident Data Where WildMatch(Type,'*discount*') > 0;
Concatenate
Load Type,
'' as [Discount Amount],
Amount as [Policy Amount]
Resident Data Where WildMatch(Type,'*policy*') > 0;

Drop Table Data;

View solution in original post

3 Replies
jwjackso
Specialist II
Specialist II

With data

Data:
Load * Inline [
Type,Amount
abc discount,30
xyz policy,100
abc discount,80
xyz policy,50
];

NewData:
Load Type,
Amount as [Discount Amount],
'' as [Policy Amount]
Resident Data Where WildMatch(Type,'*discount*') > 0;
Concatenate
Load Type,
'' as [Discount Amount],
Amount as [Policy Amount]
Resident Data Where WildMatch(Type,'*policy*') > 0;

Drop Table Data;

View solution in original post

sanp96
Contributor III
Contributor III
Author

Can I use something like this in the chart expression? Instead of data load?

jwjackso
Specialist II
Specialist II

If you have [Type] for the dimension, you could have measures like

Sum({<[Type]={"*discount*"}>}[Amount])

and

Sum({<[Type]={"*policy*"}>}[Amount])