Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
You can look here to clean your data
How do you distinguish between small and large carrier? Is it based on percentage or top n? And this is needed in the script?
Say, I know already in the requirement specifically the 4 carriers needs to be displayed rest everything should be moved to others.
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;
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.
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;
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.
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;
Thanks for reply and let me try
So this is step 2 correct after separation of 4 carriers and Others correct?
Thanks.
So this is step 2 correct after separation of 4 carriers and Others correct?
I have no idea what you are asking here