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.
BI Architect Consultant
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.
try this code. Assuming that Customer is your main table and "Customer check" your INLINE table:
After "Customer check" add
LOAD Customer as CustomerTmp Resident Customer;
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.
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.