Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
melissapluke
Partner - Creator
Partner - Creator

Creating flags for keys with multiple rows

I have a table like this:

ID Code Code Type Dollar Amount

1 111 Red $100

1 112 Red $150

1 113 Green $120

1 114 Blue $50

2 117 Green $170

2 119 Blue $3

2 119 Yellow $30

3 201 Red $1

I want to create a flag of some sort to be able to count totals of the code type either by ID or overall.
For example:
ID 1 I would want a total of $250 for red, $120 for green and $50 for blue.
Overall, I would want a total of $251 for red, $290 for green, $53 for blue, and $30 for yellow.

Finally, I want to be able to exclude flags. So for example, if I wanted to include IDs that had red and exclude IDs that had a yellow associated with it, I'd exclude all of the values for ID 2, and only total the sums for ID 1 and 3, giving me a total of $251 for red, $120 for green and $50 for blue . Any suggestions would be appreciated.

2 Solutions

Accepted Solutions
sunny_talwar

I might not not have full understood your issue, but don't you already have a flag in the form of Code Type? In the screenshot below, when I select Code Type = Red, I get this

image.png

Using the expression

=Sum({<[Code Type], ID = P(ID)>}[Dollar Amount])

View solution in original post

sunny_talwar

But this one expression will work for all different selection of Code Type. For example, if you select Green, You will get this

image.png

While selection Yellow give this

image.png

If you still want a script based solution, I can help you with that using a LinkTable concept.

View solution in original post

10 Replies
sunny_talwar

Why do you need flags for this? You data is already aligned for the things that you mentioned. For first part of your question you can just create a visual with ID and [Code Type] as dimension and and Sum([Dollar Amount]) as the expression.

For the second part you can use set analysis to achieve this. I am not sure why you need flags here? Can you provide more details as to what the end goal is?

melissapluke
Partner - Creator
Partner - Creator
Author

Hi Sorry about that! I had been so focused on it, I completely mis-explained things. The totals I explained are what it is doing NOW with the "Code Type" field. I want to total the sum of the amount for anything that included a code type. So if i wanted to get a total for "red" code types, I'd sum all of the amounts for ID 1 and ID 3. So $421 for Red.  After posting this, I figured out that if i create a separate table for each Code type like shown below, I am able to achieve this, but am wondering if there is a better way.

TempRed:
Load distinct
ID as ID2
1 as RedFlag
resident Table where "Code Type"='Red';
Load distinct
ID as ID2
1 as RedFlag
resident Table where not "Code Type"='Red' and not exists(ID2, ID);

Red:
Load distinct
ID2 as ID
RedFlag
resident TempRed;

Drop Table Red.

Repeat for each color. 

Any better ideas?

sunny_talwar

But are you looking to do this in the script? Can you may be do this on the front end of the app? It might be much easier to do using set analysis.

melissapluke
Partner - Creator
Partner - Creator
Author

Yes, I want to be able to do it in a script. I would like a user to be able to see the sum(amount), and then if they pick a flag, such as the red flag, they'd see sum(amount) for only IDs that contain red.
sunny_talwar

I might not not have full understood your issue, but don't you already have a flag in the form of Code Type? In the screenshot below, when I select Code Type = Red, I get this

image.png

Using the expression

=Sum({<[Code Type], ID = P(ID)>}[Dollar Amount])
melissapluke
Partner - Creator
Partner - Creator
Author

I don't want to do this in set analysis. I want to user to be able to singularly look at sum(amount) and adjust the total with flags, rather than having to create a Sum({<[Code Type], ID = P(ID)>}[Dollar Amount]) for each different Code Type.
sunny_talwar

But this one expression will work for all different selection of Code Type. For example, if you select Green, You will get this

image.png

While selection Yellow give this

image.png

If you still want a script based solution, I can help you with that using a LinkTable concept.

sunny_talwar

Script based solution

Table:
LOAD * INLINE [
    ID, Code, Code Type, Dollar Amount
    1, 111, Red, $100
    1, 112, Red, $150
    1, 113, Green, $120
    1, 114, Blue, $50
    2, 117, Green, $170
    2, 119, Blue, $3
    2, 119, Yellow, $30
    3, 201, Red, $1
];

For i = 1 to FieldValueCount('Code Type')

	LET vCodeType = FieldValue('Code Type', $(i));

    LinkTable:
    LOAD DISTINCT ID,
         '$(vCodeType)'& 'Flag' as Flag
    Resident Table
    Where [Code Type] = '$(vCodeType)';

NEXT

and now you can create a filter object for Flag and then select RedFlag within Flag and Sum([Dollar Amount]) will show you 421

image.png

melissapluke
Partner - Creator
Partner - Creator
Author

Sorry I was reading your initial replies via email and didn't see the screenshots. This works, thank you!