topic Re: How to Create a FLAG Column in the Script by Dimension in App Development

How to Create a FLAG Column in the Script by Dimension

Anonymous — Wed, 22 Nov 2017 15:00:02 GMT

I need to create a flag column in the script for the below table.

The condition is: if a single Trip Number e.g (00001) contains an [Expense Type] that has 'Meal A' and 'Meal B' then flag both rows with an 'X' in a separate column.

I have tried a number of different things but not having any success,

Any help would be much appreciated.

Thanks in advance!!!

Trip Number	Expense Type	Amount
00001	Meal A	10.00
00001	Meal B	15.00
00001	Air	1000.00
00002	Meal A	5.00
00002	Air	800.00
00002	Taxi	5.00

Desired Output

Trip Number	Expense Type	Amount	FLAG
00001	Meal A	10.00	X
00001	Meal B	15.00	X
00001	Air Meal A	1000.00
00002	Air	5.00
00002	Taxi	800.00

Re: How to Create a FLAG Column in the Script by Dimension

YoussefBelloum — Wed, 22 Nov 2017 15:18:07 GMT

Hi,

try this on the Flag expression:

=if(match([Expense Type],'Meal A','Meal B'), 'X')

Re: How to Create a FLAG Column in the Script by Dimension

antoniotiman — Wed, 22 Nov 2017 15:24:12 GMT

May be this

Table:
LOAD [Trip Number],
[Expense Type],
Amount,If(Match([Expense Type],'Meal A','Meal B'),AutoNumber(RowNo(),[Trip Number])) as Count
FROM
https://community.qlik.com/message/1387306
(html, codepage is 1252, embedded labels, table is @1);

Left Join
LOAD [Trip Number],Max(Count) as MaxCount
Resident Table
Group By [Trip Number];
LOAD *,If(MaxCount=2 and IsNum(Count),'X',' ') as Flag
Resident Table;
Drop Table Table;

Re: How to Create a FLAG Column in the Script by Dimension

antoniotiman — Wed, 22 Nov 2017 15:52:46 GMT

However, if You have data like this

Table:
LOAD *,If(Match([Expense Type],'Meal A'),1) as CountA,
If(Match([Expense Type],'Meal B'),2) as CountB
Inline [
Trip Number,Expense Type,Amount
00001, Meal A, 10.00
00001, Meal B, 15.00
00001, Air, 1000.00
00002, Meal A, 5.00
00002, Meal A, 51.00
00002, Air,800.00
00002, Taxi, 5.00
00001, Meal B, 16.00
];
Left Join
LOAD [Trip Number],RangeSum(Max(CountA),Max(CountB)) as MaxCount
Resident Table
Group By [Trip Number];
LOAD *,If(MaxCount=3 and (IsNum(CountA) or IsNum(CountB)),'X',' ') as Flag
Resident Table;
Drop Table Table;

Re: How to Create a FLAG Column in the Script by Dimension

Anonymous — Wed, 22 Nov 2017 16:12:09 GMT

Hi Antonio

Thanks for your prompt reply, solution number 2 works!

I will have to test with real data but should be fine.

Thanks again!!!

Re: How to Create a FLAG Column in the Script by Dimension

StefanE — Wed, 08 Feb 2023 16:31:50 GMT

Hello Everyone,

Can I ask a follow-up question after all this time? Is it possible in Script or Chart function to flag - in this case - the Trip Number if one of the Expense Types is "Meal C"?

Other wise I'll open a new post.

Thanks in advance and kind regards

Stefan