Segregating nulls among registers

[Anonymous]

Hello everyone, I'm new to Qlickview and I'm trying to automate an excel spreadsheet via qlick, rather than a macro.

I am creating ifs to assign a value to the Customer field, depending on the conditions.

For example:

if [Customer_Code] = '5' or [Customer_Code] = '5B' or [Customer_Code] = '5XS' or [Customer_Code] = '6XS'

      if ([Customer_Code] = '7' or [Customer_Code] = '7B', 'Customer B', ...............

           if (IsNull (Customer_Code), 'NULL') as Customer

The question is: there are some NULLS, a variable amount, I wanted when the values were null, that I make a distribution as follows:

Of all NULLS, 2/3 is assigned to Client A, and the rest 1/3, 50% for Client B and 50% for Client C.

I need to do this because I use another column for financial control and this column is segregated by client.

How can I solve this problem?

Thanks!

[Anonymous]

Is this possible?

[sunny_talwar]

Would you be able to share some sample data with the expected output?

[Anonymous]

Hello Sunny, sure!

As follows, my output needs to be the sum of a cost column, [EXE RATEIO V FORTE], displayed in the dynamic table.

The values for NULL in the column Customer should be split into the, Customer A, B and C registers, following the rule:

of all NULLS, 2/3 go for Customer A, and the 1/3 left split 50% for Customer B and 50% for Customer C.

And so I can map the whole cost.

Thanks in advance for your attention Sunny...

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD *,

    If(Customer_Temp = 'NULL', Pick(Mod(AutoNumber(RowNo(), Customer_Temp), 6)+1, 'Customer C', 'Customer A', 'Customer A', 'Customer A', 'Customer A', 'Customer B'), Customer_Temp) as Customer;

LOAD [EXE RATEIO V_FORTE],

    Lote,

    Quantidade,

    [Estq Esp],

    Compr,

    [Plan Centro Principal],

    Customer_Code,

    If([Customer_Code] = '5' or [Customer_Code] = '5B' or [Customer_Code] = '5XS' or [Customer_Code] = '6XS', 'Customer A',

      If([Customer_Code] = '7' or [Customer_Code] = '7B', 'Customer B',

      If([Customer_Code] = 'BC', 'Customer C', 'NULL'))) as Customer_Temp

FROM

[Line Items.xls]

(biff, embedded labels, table is [LINE ITEMS$]);

[Anonymous]

That's exactly it!

Thank you Sunny...