Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

If Count of ID >=3 Then Count as 1

Afternoon,

I'm adding some IF statements to my script to group into types: Packs, Products and Other. If there are 3 or more products under one ID number, I need this to show as a pack, any ideas how I can do this?

Thanks,

Rob

Tags (2)
5 Replies
ecolomer
Not applicable

Re: If Count of ID >=3 Then Count as 1

can you explain your need more, please. O upload an simple example. TX

Not applicable

Re: If Count of ID >=3 Then Count as 1

Script below - I need to add an additional IF statement in my second load to create type 'pack' if count of SpiderRef is greater than or equal to 3.

Thanks

Invoice:

LOAD

    "Type",

    Ref as SageRef,

    Nominal,

    "Date",

    "Invoice No",

    Net as Revenue,

    VAT,

    ProductName,

    PackName,

    SubField("Invoice No",'-',1) as SpiderRef,

    If(PackName='',ProductName,PackName) as "Pack/Product"

FROM [lib://Account Performance/S2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Filtering:

Load

    SpiderRef,

    IF(PackName>0,'Pack',

    IF(Nominal=4000 AND VAT=0,'OLASDisb',

    IF(Nominal=4001 AND VAT=0,'OLASDisb','Product'))) as SaleType

Resident Invoice;

ecolomer
Not applicable

Re: If Count of ID >=3 Then Count as 1

Try to grouped clause in order to group the record with the same ID

Gabriel
Not applicable

Re: If Count of ID >=3 Then Count as 1

Hi,

Sorry this is still not clear. Are you able to post sample data?

settu_periasamy
Not applicable

Re: If Count of ID >=3 Then Count as 1

Hi

try like this..

create a group by table.. then you can do the filtering table.

Group:

Load

    SpiderRef,

Count (Spider) as count_spiderref,

    PackName,

    Nominal,

   Sum (VAT) as VAT,

Resident Invoice GROUP BY

SpiderRef,

PackName,

Nominal;

Filtering:

Load

    SpiderRef,

    IF(count_spiderref>0,'Pack',

    IF(Nominal=4000 AND VAT=0,'OLASDisb',

    IF(Nominal=4001 AND VAT=0,'OLASDisb','Product'))) as SaleType

Resident Group;