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

[sanp96]

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

Type	Amount
abc discount	30
xyz policy	100
abc discount	80
xyz policy	50

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:

Type	Discount amount	Policy Amount
	110	150
abc discount	30	-
xyz policy	-	100
abc discount	80	-
xyz policy	-	50

Any help with this is highly appreciated. 

Thanks!

[jwjackso · Accepted solution]

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;

[sanp96]

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

[jwjackso]

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

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

and

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