Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

alex59800
New Contributor

Invoice Flag - Flag invoices number with specific products code

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

   

InvoiceProductFlag
A1
A4Promo
A5Promo
A7Promo
A8
B2
B3
B1
B5
C4
C5
C6
C9
C10
D1
D2
D4Promo
D5Promo
D7Promo
D9

 

3 Replies

Re: Invoice Flag - Flag invoices number with specific products code

An expression based solution:

Dimension

Invoice

Product

Expression

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

Re: Invoice Flag - Flag invoices number with specific products code

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;

Re: Invoice Flag - Flag invoices number with specific products code

Expression based

Dimension

Invoice

Product

Expression

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