Invoice Flag - Flag invoices number with specific products code

[alex59800]

Hello Qlik members,

I got a question concerning flag.

I need to apply a flag for invoices when i found a certain combination of products code. The Flag need to be apply only on combination of Invoice Number & product code

For example need to put a "PROMO Flag" if i found codes 4,5 and 7. Below Invoice DB structure :

Any idea if it's easier to do through Expresssion or Script ? (I remember a possible solution trough Possible P() expression with set analysis, but no insurance with the syntax.

Thanks a lot

Invoice	Product	Flag
A	1
A	4	Promo
A	5	Promo
A	7	Promo
A	8
B	2
B	3
B	1
B	5
C	4
C	5
C	6
C	9
C	10
D	1
D	2
D	4	Promo
D	5	Promo
D	7	Promo
D	9

[sunny_talwar]

An expression based solution:

Dimension

Invoice

Product

Expression

Only({<Invoice = p({<Product = {'4', '5', '7'}>}), Product = {'4', '5', '7'}>} 'Promo')

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    Invoice, Product

    A, 1

    A, 4

    A, 5

    A, 7

    A, 8

    B, 2

    B, 3

    B, 1

    B, 5

    C, 4

    C, 5

    C, 6

    C, 9

    C, 10

    D, 1

    D, 2

    D, 4

    D, 5

    D, 7

    D, 9

];

Temp:

LOAD Invoice,

If(Count(Product) = 3, 'Promo') as Flag

Resident Table

Where Match(Product, 4, 5, 7)

Group By Invoice;

Left Join (Temp)

LOAD Distinct Product

Resident Table

Where Match(Product, 4, 5, 7);

Left Join (Table)

LOAD *

Resident Temp;

[sunny_talwar]

Expression based

Dimension

Invoice

Product

Expression

If(Count(DISTINCT TOTAL <Invoice> {<Product = {4, 5, 7}>} Product) = 3 and Match(Product, 4, 5, 7), 'Promo')