TABULAR CHART REQ

[Purushothaman]

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!

[IamAlbinAntony · Accepted solution]

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;

[vinieme12 · Accepted solution]

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;

[Purushothaman]

Hi @vinieme12 ,

Many thanks for your great help!!! Many I know how you did the sorting? Can you please tell me?

Thank you!!

[vinieme12]

for L1 sort by Expression >> l1_sort  Ascending

for L2 >> sort by expression >> l2_sort Ascending