How to Create a FLAG Column in the Script by Dimension

[Anonymous]

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

[YoussefBelloum]

Hi,

try this on the Flag expression:

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

[antoniotiman]

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;

[antoniotiman · Accepted solution]

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;

[Anonymous]

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!!!

[StefanE]

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