Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a situation where i concatenate 3 tables , where the last 2 IS just a subset of data from the same excel file as the first.
What i need is to create the exists or match to be dynamicaly selected from a excel spreadsheet instead of fixed as i currently do.
I have a customer map excel sheet with only subset of customers and suppliers names. So the one concatenate is a filter on just a few customers and the second concatenate is a filter on a few suppliers - this is the excel that must be used for the filtering dynamically
The logic of the data is. MySupplier(this is me) deliver to customers. MySupplier will be in the supplier field and the customername will be in the customer field
My customers can also be wholesalers who sell again to customers. But in this case these wholesaler who i am selling to will be in the customer field
Then again a wholesaler can sell to cusotmers the the wholesaler will be in the supplier field and his customers will be in the customer field.
My end result that i am interrested in as you will see in the example is - what did these wholesaler that buy from me bought in and what did they sell out to customers. These calculations i got right, my only problem is to create a list in the where clause out of a excel spreadsheet instead of me typing the names in, in the script .
See attached demo model and the two excel spreadsheets that i use
Help would really be appreciated
Thanks Louw
Hi Louw,
Attached is 2 qvws. The MyWholesale Concatenate does what you want, the script looks as follows:
CustomerMap: // TO PICK UP ONE NAME FOR CUSTOMER AND WHERE CUSTOMER IS ALSO A SUPPLIER
Mapping LOAD Customer,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
SupplierMap: // TO PICK UP ONE NAME FOR SUPPLIER AND WHERE SUPPLIER IS ALSO A CUSTOMER
Mapping LOAD Supplier,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
Transactions:
LOAD Supplier,
Customer,
NetSales,
'ALL' AS Salestype
FROM
(ooxml, embedded labels, table is Sheet1);
Concatenate(Transactions)
LOAD Supplier,
Customer,
NetSales AS InSales,
ApplyMap('CustomerMap',Customer, null()) AS MapName,
'IN' AS Salestype
Resident Transactions
WHERE not isnull(ApplyMap('CustomerMap',Customer, null()));
Concatenate(Transactions)
LOAD Supplier,
Customer,
NetSales AS OutSales,
ApplyMap('SupplierMap',Supplier, null()) AS MapName,
'OUT' AS Salestype
Resident Transactions
Where not isnull(ApplyMap('SupplierMap',Supplier, null()));
The MyWholesale Different Model reduces the number of records you bring in by either assigning the record as In, Out, or Other and also maps Customer if it matches any mapping. If it doesnt match any Customer, then it matches supplier. If it doesnt match either customer or Supplier, the MapName becomes null. Also changed some expressions in the dashboard for it to render correctly. The script for that one is:
CustomerMap:
Mapping LOAD Customer,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
SupplierMap: // TO PICK UP ONE NAME FOR SUPPLIER AND WHERE SUPPLIER IS ALSO A CUSTOMER
Mapping LOAD Supplier,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
Transactions:
LOAD Supplier,
Customer,
NetSales,
ApplyMap('CustomerMap',Customer, ApplyMap('SupplierMap',Supplier, null())) as MapName,
if(not isnull(ApplyMap('CustomerMap',Customer, null())), 'IN', if(not isnull(ApplyMap('SupplierMap',Supplier, null())), 'OUT', 'OTHER')) as Salestype
FROM
(ooxml, embedded labels, table is Sheet1);
Hope this helps!
Hi Louw,
Attached is 2 qvws. The MyWholesale Concatenate does what you want, the script looks as follows:
CustomerMap: // TO PICK UP ONE NAME FOR CUSTOMER AND WHERE CUSTOMER IS ALSO A SUPPLIER
Mapping LOAD Customer,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
SupplierMap: // TO PICK UP ONE NAME FOR SUPPLIER AND WHERE SUPPLIER IS ALSO A CUSTOMER
Mapping LOAD Supplier,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
Transactions:
LOAD Supplier,
Customer,
NetSales,
'ALL' AS Salestype
FROM
(ooxml, embedded labels, table is Sheet1);
Concatenate(Transactions)
LOAD Supplier,
Customer,
NetSales AS InSales,
ApplyMap('CustomerMap',Customer, null()) AS MapName,
'IN' AS Salestype
Resident Transactions
WHERE not isnull(ApplyMap('CustomerMap',Customer, null()));
Concatenate(Transactions)
LOAD Supplier,
Customer,
NetSales AS OutSales,
ApplyMap('SupplierMap',Supplier, null()) AS MapName,
'OUT' AS Salestype
Resident Transactions
Where not isnull(ApplyMap('SupplierMap',Supplier, null()));
The MyWholesale Different Model reduces the number of records you bring in by either assigning the record as In, Out, or Other and also maps Customer if it matches any mapping. If it doesnt match any Customer, then it matches supplier. If it doesnt match either customer or Supplier, the MapName becomes null. Also changed some expressions in the dashboard for it to render correctly. The script for that one is:
CustomerMap:
Mapping LOAD Customer,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
SupplierMap: // TO PICK UP ONE NAME FOR SUPPLIER AND WHERE SUPPLIER IS ALSO A CUSTOMER
Mapping LOAD Supplier,
MapName
FROM
(ooxml, embedded labels, table is Sheet1);
Transactions:
LOAD Supplier,
Customer,
NetSales,
ApplyMap('CustomerMap',Customer, ApplyMap('SupplierMap',Supplier, null())) as MapName,
if(not isnull(ApplyMap('CustomerMap',Customer, null())), 'IN', if(not isnull(ApplyMap('SupplierMap',Supplier, null())), 'OUT', 'OTHER')) as Salestype
FROM
(ooxml, embedded labels, table is Sheet1);
Hope this helps!
Hi Jeremiah,
Thank you for your help. I had a look at both examples. And at the en i have implemented your MyWholesale Different method and i work really great in my actual model.
Much appreciated
Regards
Louw