5 Replies Latest reply: Oct 30, 2012 5:17 AM by DanieleC RSS

    Inline table "others" or *

      I have a very large table with customers, where only a few of them have an extra indicator for which I have used an inline table:

       

      -------------------------

       

      [Customer check]:

       

       

      LOAD * INLINE [

          Customer, Check

          A, Check ok

          B, Check ok

          N, Check not ok

          Z, Check ok

       

      ]:

       

      ---------------------------

       

      For all other customers (C, D, M etc) I want to have value "No check" under "check" without having to fill in the whole table.

       

      Thanks!

        • Re: Inline table "others" or *
          Magnus ÅVITSLAND

          Hi hollanderndj.

           

          You can make your Customer Check table a mapping table, and when you load your "real" customer table, you can do an ApplyMap against your mapping table to find customers with check not ok. This should give you a table with all customers not in Map_CustomerCheck will have Check = No check.

           

          Map_CustomerCheck:
          MAPPING LOAD * INLINE [
              Customer, Check
              A, Check ok
              B, Check ok
              Z, Check ok
          ]:
          

           

          RealCustomerTable:
          LOAD *, ApplyMap('MapCustomerCheck', CustomerID, 'No check') as Check;
          

           

          The first parameter of ApplyMap is the name of the mapping table enclosed with single quotation marks.

          Second parameter is the field to pass.

          Third parameter is the value to be returned if no match in the mapping table.

           

          Hope this helps.

           

           

          Kind regards

           

          Magnus Åvitsland

          BI Architect Consultant

          Framsteg Business Intelligence Corp.

            • Re: Inline table "others" or *

              Hi Magnus,

               

              Thanks! This solution does not work tough, because I have a left join before the small customer check table with the bigger table and QV comments that these can not be used together . So I have:

               

              A table with all customers -->

               

              An inline to indicate which customers are checked and which not  -->

               

              And then I need an indcator for all other customers using both tables.

                • Re: Inline table "others" or *

                  Hello Hollanderndj,

                  try this code. Assuming that Customer is your main table and "Customer check" your INLINE table:

                   

                  After "Customer check" add

                   

                  TmpTab:

                  LOAD  Customer as CustomerTmp Resident Customer;

                  left join

                  LOAD  Customer as CustomerTmp, Check as CheckTmp Resident [Customer check];

                   

                  LOAD Distinct CustomerTmp as Customer, if(len(CheckTmp) >0, CheckTmp, 'Missing Check') as Check Resident TmpTab;

                   

                  Drop Table TmpTab;

                   

                  You can change 'Missing Check' with what you prefer. After reloaded the document check the table "Customer check", you should have all your customers in it.

                   

                  Regards,

                  Daniele

                    • Re: Inline table "others" or *

                      I think I can get this working, but I am confused where you mean the field "customer" and where the table "customer" . How does the script look if you use the following names:

                       

                      Big customer table = CustomerTable

                      Inline table = CheckTable

                      Customer ref = CustomerName

                      Check = Check

                       

                      Thanks again!!!!

                        • Re: Inline table "others" or *

                          Hello Hollanderndj,

                             this is the example with your names. Take care that "CustomerName" must to be used in your CustomerTable and INLINE table CheckTable, now in your example (first post) you are using "Customer" (and this is why I was using it in my example).

                               

                          CustomerTable:
                          Load CustomerName, ....
                          
                          CheckTable:
                          LOAD * INLINE [
                          CustomerName, Check
                          .......
                          ];
                                
                          TmpTab:
                          LOAD  CustomerName as CustomerTmp Resident CustomerTable;
                          left join
                          LOAD  CustomerName as CustomerTmp, Check as CheckTmp Resident CheckTable;
                          
                          LOAD Distinct CustomerTmp as CustomerName, if(len(CheckTmp) >0, CheckTmp, 'Missing Check') as Check Resident TmpTab;
                          
                          Drop Table TmpTab;
                          

                           

                            

                          Now check CheckTable, this should work.

                           

                          Regards,

                          Daniele