Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am struggling to fix this requirement as per the below snapshot.
May I request anyone to help me with this? I have attached the dataset with this.
Many Thanks!! Hoping for a Help!
Use this, check QVF app for sorting order
Raw:
LOAD
COUNTRY_ABB&BRAND As Inline_key,
COUNTRY_ABB,
BRAND,
"YEAR",
"MONTH",
PERIOD,
SalesUSD
FROM [lib://DataFiles/C-BRAND_QlikCommunity.xlsx]
(ooxml, embedded labels, table is [Raw Data]);
Inline_BRAND_table:
Load * Inline [
Sno, Inline_BRAND, BRAND
1, TOTAL, Apple
2, TOTAL, HUAWEI
3, TOTAL, MI/REDMI
4, TOTAL, OPPO
5, TOTAL, Samsung
6, TOTAL, VIVO
7, Samsung, Samsung
8, Apple, Apple
9, C-BRAND, MI/REDMI
10, C-BRAND, OPPO
11, MI/REDMI, MI/REDMI
12, OPPO, OPPO
13, Others, HUAWEI
14, Others, VIVO
15, HUAWEI, HUAWEI
];
Join(Inline_BRAND_table)
Inline_COUNTRY_ABB_table:
Load
COUNTRY_ABB
Resident Raw;
NoConcatenate
Inline:
Load 'All Countries Total' As Inline_COUNTRY_ABB,
Sno,
COUNTRY_ABB,
Inline_BRAND,
BRAND,
COUNTRY_ABB&BRAND As Inline_key
Resident Inline_BRAND_table;
Concatenate (Inline)
Inline:
Load COUNTRY_ABB As Inline_COUNTRY_ABB,
Sno,
COUNTRY_ABB,
Inline_BRAND,
BRAND,
COUNTRY_ABB&BRAND As Inline_key
Resident Inline_BRAND_table;
Drop Table Inline_BRAND_table;
Drop Fields COUNTRY_ABB,BRAND from Raw;
raw:
LOAD
COUNTRY_ABB,
BRAND,
"YEAR",
"MONTH",
PERIOD,
SalesUSD,
pick(WildMatch(BRAND,'samsung','apple','oppo','mi/redmi')+1,'Others','SAMSUNG','APPLE','C-BRAND','C-BRAND') as prod_group,
COUNTRY_ABB&BRAND as Key
FROM [lib://AttachedFiles/C-BRAND_QlikCommunity.xlsx]
(ooxml, embedded labels, table is [Raw Data]);
CountryGroup:
Load
Distinct COUNTRY_ABB
,'All Countries' as L1
,1 as l_sort
Resident raw;
Concatenate(CountryGroup)
Load
Distinct COUNTRY_ABB
,repeat(chr(160),10)&COUNTRY_ABB as L1
,wildmatch(COUNTRY_ABB,'AU','VN','NZ')+1 as l_sort
Resident raw;
temp_prodGroup:
Load
Distinct BRAND
,'TOTAL' as L2
Resident raw;
Concatenate(temp_prodGroup)
Load
Distinct BRAND
,prod_group as L2
Resident raw;
Load
Distinct BRAND
,if(wildmatch(prod_group,'C-BRAND') or wildmatch(BRAND,'Huawei') ,repeat(chr(160),10)&BRAND) as L2
Resident raw
Where wildmatch(prod_group,'C-BRAND','Others');
prodGroup:
Load BRAND
,L2
,wildmatch(L2,'TOTAL','SAMSUNG','APPLE','C-BRAND','*oppo','*mi/redmi','Others','*Huawei') as l2_sort
Resident temp_prodGroup;
drop table temp_prodGroup;
drop field prod_group;
exit Script;
Use this, check QVF app for sorting order
Raw:
LOAD
COUNTRY_ABB&BRAND As Inline_key,
COUNTRY_ABB,
BRAND,
"YEAR",
"MONTH",
PERIOD,
SalesUSD
FROM [lib://DataFiles/C-BRAND_QlikCommunity.xlsx]
(ooxml, embedded labels, table is [Raw Data]);
Inline_BRAND_table:
Load * Inline [
Sno, Inline_BRAND, BRAND
1, TOTAL, Apple
2, TOTAL, HUAWEI
3, TOTAL, MI/REDMI
4, TOTAL, OPPO
5, TOTAL, Samsung
6, TOTAL, VIVO
7, Samsung, Samsung
8, Apple, Apple
9, C-BRAND, MI/REDMI
10, C-BRAND, OPPO
11, MI/REDMI, MI/REDMI
12, OPPO, OPPO
13, Others, HUAWEI
14, Others, VIVO
15, HUAWEI, HUAWEI
];
Join(Inline_BRAND_table)
Inline_COUNTRY_ABB_table:
Load
COUNTRY_ABB
Resident Raw;
NoConcatenate
Inline:
Load 'All Countries Total' As Inline_COUNTRY_ABB,
Sno,
COUNTRY_ABB,
Inline_BRAND,
BRAND,
COUNTRY_ABB&BRAND As Inline_key
Resident Inline_BRAND_table;
Concatenate (Inline)
Inline:
Load COUNTRY_ABB As Inline_COUNTRY_ABB,
Sno,
COUNTRY_ABB,
Inline_BRAND,
BRAND,
COUNTRY_ABB&BRAND As Inline_key
Resident Inline_BRAND_table;
Drop Table Inline_BRAND_table;
Drop Fields COUNTRY_ABB,BRAND from Raw;
raw:
LOAD
COUNTRY_ABB,
BRAND,
"YEAR",
"MONTH",
PERIOD,
SalesUSD,
pick(WildMatch(BRAND,'samsung','apple','oppo','mi/redmi')+1,'Others','SAMSUNG','APPLE','C-BRAND','C-BRAND') as prod_group,
COUNTRY_ABB&BRAND as Key
FROM [lib://AttachedFiles/C-BRAND_QlikCommunity.xlsx]
(ooxml, embedded labels, table is [Raw Data]);
CountryGroup:
Load
Distinct COUNTRY_ABB
,'All Countries' as L1
,1 as l_sort
Resident raw;
Concatenate(CountryGroup)
Load
Distinct COUNTRY_ABB
,repeat(chr(160),10)&COUNTRY_ABB as L1
,wildmatch(COUNTRY_ABB,'AU','VN','NZ')+1 as l_sort
Resident raw;
temp_prodGroup:
Load
Distinct BRAND
,'TOTAL' as L2
Resident raw;
Concatenate(temp_prodGroup)
Load
Distinct BRAND
,prod_group as L2
Resident raw;
Load
Distinct BRAND
,if(wildmatch(prod_group,'C-BRAND') or wildmatch(BRAND,'Huawei') ,repeat(chr(160),10)&BRAND) as L2
Resident raw
Where wildmatch(prod_group,'C-BRAND','Others');
prodGroup:
Load BRAND
,L2
,wildmatch(L2,'TOTAL','SAMSUNG','APPLE','C-BRAND','*oppo','*mi/redmi','Others','*Huawei') as l2_sort
Resident temp_prodGroup;
drop table temp_prodGroup;
drop field prod_group;
exit Script;
Hi @vinieme12 ,
Many thanks for your great help!!! Many I know how you did the sorting? Can you please tell me?
Thank you!!
for L1 sort by Expression >> l1_sort Ascending
for L2 >> sort by expression >> l2_sort Ascending