Qlik Table - Change values and aggregate Data

[Anonymous]

Hi Team,

I have an question which may be easy for you. Hope to you can help me. I have a table name 'CarrierCustomer'

CarrierCustomer:

Carrier_ID                Customers

ATT                              40000

TMB                            30000 

VZN                            50000     

SPR                              20000   

CRT                              10000 

MET                              30000

LYC                                10000

But I want to load this table to my Qlik memory like this (here is my requirement)

Give proper names to the short names of Carrier and also aggregate sum all the small carrier as one named as 'OTHERS'

The loaded table on Qlik memory looks like this

CarrierCustomer_new:

Carrier_ID                Customers

AT and T                              40000

T- Mobile                              30000 

Verizon                                  50000     

Sprint                                      20000 

Others                                  50000

What I have done here is

• I have given proper names to my Carrier Short Names
• I have aggregated (SUM)  customers of rest of the small carriers and displayed as Others (50000)

Let me know how to implement this. Say here is my load script for orignal table.

LOAD

    Carrier_ID,

   Customers

FROM [lib://test(qlikadmin)/CarrierCustomer.qvd]

(qvd);

The help is appreciated. Thank you.

[sunny_talwar]

You can look here to clean your data

Data Cleansing‌

How do you distinguish between  small and large carrier? Is it based on percentage or top n? And this is needed in the script?

[Anonymous]

Say, I know already  in the requirement specifically the 4 carriers needs to be displayed rest everything should be moved to others.

[sunny_talwar]

Try this script

Table:

LOAD * Inline [

Carrier_ID,                Customers

ATT,                       40000

TMB,                       30000

VZN,                       50000    

SPR,                       20000  

CRT,                       10000

MET,                       30000

LYC,                       10000

];

TmpTable:

LOAD Carrier_ID,

Customers,

RowNo() as Rank

Resident Table

Order By Customers desc;

FinalTable:

NoConcatenate

LOAD Carrier_ID,

Customers

Resident TmpTable

Where Rank <= 4;

Concatenate (FinalTable)

LOAD 'Others' as Carrier_ID,

Sum(Customers) as Customers

Resident TmpTable

Where Rank > 5;

DROP Table Table, TmpTable;

[Anonymous]

can you rewrite the same with carrier names not by the number of customers. Because my real table does not have number of customers, I just gave an example.

I want to change the short names of ATT, TMB, VZN, SPR  to Full names and move rest of everything as 'Others', irrespective of number of customers.

Thanks for all your support.

[sunny_talwar]

This

Table:

LOAD * Inline [

Carrier_ID,                Customers

ATT,                       40000

TMB,                       30000

VZN,                       50000    

SPR,                       20000  

CRT,                       10000

MET,                       30000

LYC,                       10000

];

FinalTable:

NoConcatenate

LOAD Carrier_ID,

Customers

Resident Table

Where Match(Carrier_ID, 'ATT', 'VZN', 'SPR', 'TMB');

Concatenate (FinalTable)

LOAD 'Others' as Carrier_ID,

Sum(Customers) as Customers

Resident Table

Where not Match(Carrier_ID, 'ATT', 'VZN', 'SPR', 'TMB');

DROP Table Table;

[Anonymous]

This looks great but how can I rename them

ATT  to AT and T, VZN to Verizon, SPR to Sprint and TMB to T-Mobile?

Can I do it at the time of load only? Should I do that on final table?

Thanks.

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * Inline [

Carrier_ID,                Customers

ATT,                      40000

TMB,                      30000

VZN,                      50000   

SPR,                      20000 

CRT,                      10000

MET,                      30000

LYC,                      10000

];

FinalTable:

NoConcatenate

LOAD Pick(Match(Carrier_ID, 'ATT', 'VZN', 'SPR', 'TMB'), 'AT and T', 'Verizon', 'Sprint', 'T-Mobile') as Carrier_ID,

Customers

Resident Table

Where Match(Carrier_ID, 'ATT', 'VZN', 'SPR', 'TMB');

Concatenate (FinalTable)

LOAD 'Others' as Carrier_ID,

Sum(Customers) as Customers

Resident Table

Where not Match(Carrier_ID, 'ATT', 'VZN', 'SPR', 'TMB');

DROP Table Table;

[Anonymous]

Thanks for reply and let me try

So this is step 2 correct after separation of 4 carriers and Others correct?

Thanks.

[sunny_talwar]

So this is step 2 correct after separation of 4 carriers and Others correct?

I have no idea what you are asking here