Inline table "others" or *

[#1]

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!

[magavi_framsteg]

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.

[#3]

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.

[#4]

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

[#5]

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!!!!

[#6 · Accepted solution]

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