2 Replies Latest reply: Dec 18, 2014 12:53 PM by Louwrie Terblanche RSS

    where exist from list in concatenate

    Louwrie Terblanche

      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

        • Re: where exist from list in concatenate
          Jeremiah Kurpat

          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

          [C:\Users\kurpatj\Desktop\Community\Customers Map.xlsx]

          (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

          [C:\Users\kurpatj\Desktop\Community\Customers Map.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          Transactions:

          LOAD Supplier,

               Customer,

               NetSales,

               'ALL' AS Salestype

          FROM

          [C:\Users\kurpatj\Desktop\Community\Transactions.xlsx]

          (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

          [C:\Users\kurpatj\Desktop\Community\Customers Map.xlsx]

          (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

          [C:\Users\kurpatj\Desktop\Community\Customers Map.xlsx]

          (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

          [C:\Users\kurpatj\Desktop\Community\Transactions.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Hope this helps!