Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can you explain your need more, please. O upload an simple example. TX
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;
Try to grouped clause in order to group the record with the same ID
Hi,
Sorry this is still not clear. Are you able to post sample data?
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;