Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

5 Replies
ecolomer
Master II
Master II

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

Not applicable
Author

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
Master II
Master II

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

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

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

settu_periasamy
Master III
Master III

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;