Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
Can I use something like this in the chart expression? Instead of data load?
If you have [Type] for the dimension, you could have measures like
Sum({<[Type]={"*discount*"}>}[Amount])
and
Sum({<[Type]={"*policy*"}>}[Amount])