topic Re: Segregating nulls among registers in QlikView

Segregating nulls among registers

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Segregating nulls among registers

Anonymous — Wed, 23 May 2018 10:49:45 GMT

Is this possible?

Re: Segregating nulls among registers

sunny_talwar — Wed, 23 May 2018 11:25:13 GMT

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

Re: Segregating nulls among registers

Anonymous — Wed, 23 May 2018 13:11:48 GMT

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

Re: Segregating nulls among registers

sunny_talwar — Wed, 23 May 2018 14:14:50 GMT

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

Re: Segregating nulls among registers

Anonymous — Wed, 23 May 2018 17:18:37 GMT

That's exactly it!

Thank you Sunny...