where exist from list in concatenate

[louwriet]

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

[jerem1234 · Accepted solution]

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!

[louwriet]

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