Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Purushothaman
Partner - Creator III
Partner - Creator III

TABULAR CHART REQ

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. 

Purushothaman_1-1651809267570.png

 

Many Thanks!! Hoping for a Help!

2 Solutions

Accepted Solutions
IamAlbinAntony
Creator
Creator

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;

Capture.JPG

View solution in original post

vinieme12
Champion III
Champion III

 

qlikCommunity1.PNG

 

 

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

4 Replies
IamAlbinAntony
Creator
Creator

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;

Capture.JPG

vinieme12
Champion III
Champion III

 

qlikCommunity1.PNG

 

 

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Purushothaman
Partner - Creator III
Partner - Creator III
Author

Hi @vinieme12 ,

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

 

Thank you!!

vinieme12
Champion III
Champion III

for L1 sort by Expression >> l1_sort  Ascending

for L2 >> sort by expression >> l2_sort Ascending

qlikCommunity1.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.