Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Segregating nulls among registers

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!

1 Solution

Accepted Solutions
Highlighted

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$]);

Capture.PNG

View solution in original post

5 Replies
Highlighted
Contributor II
Contributor II

Is this possible?

Highlighted

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

Highlighted
Contributor II
Contributor II

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

Highlighted

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$]);

Capture.PNG

View solution in original post

Highlighted
Contributor II
Contributor II

That's exactly it!

Thank you Sunny...