Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
marcel_olmo
Partner Ambassador
Partner Ambassador

Crosstable Challenge. SalesMan Comissions by Product Group

Hi guys, I have an input table with the % of comission for each Product Type Sale for each SalesMan. 

The table looks like this (in the real case the SalesManID is dynamic, it's not only for 4 salesman) :

        4 5 12 14
CompanyCode FamilyCode SubFamilyCode FamilyDescription SalesmanId4 SalesManId5 SalesManId12 SalesManId14
1 ANIM ********** XXX 10% 5% 10% 10%
1 APPR ********** YYY 5% 10% 5% 10%
1 AV ********** ZZZ 10% 10% 10% 5%

 

And I'd like to get an output of a desired table like this :

FamilyCode SalesManId %Comission
ANIM 4 10%
ANIM 5 5%
ANIM 12 10%
ANIM 14 10%
APPR 4 5%
APPR 5 10%
APPR 12 5%
APPR 14 10%
AV 4 10%
AV 5 10%
AV 12 10%
AV 14 5%

 

Could anybody help me please? I don't know if crosstable it's the best option, I'm trying to do it but with no results.

PD : I've attached the example in excel, in order to make it easier for everyone to understand.

Kind regards, Marcel.

Labels (1)
1 Solution

Accepted Solutions
rodrigo_martins
Partner - Creator
Partner - Creator

Hi,

Tried this script with your sample, worked for me:

Table1:
CrossTable(SalesMan, [%Commission], 4)
LOAD
    *
FROM [lib://Dados/Sales Comission Example - v2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is InputTable);

Table2:
CrossTable(SalesManId, SalesMan, 1)
LOAD
    1 as _temp,
    *;
Load
*
FROM [lib://Dados/Sales Comission Example - v2.xlsx]
(ooxml, embedded labels, table is InputTable)
WHERE F1 = 'CompanyCode';

Left Join(Table1)
Load * Resident Table2;

Drop Field _temp;
Drop Table Table2;

rodrigo_martins_0-1715098963305.png

 

View solution in original post

5 Replies
Lisa_P
Employee
Employee

Here is the script that I would use to convert this ..

[Table]:
CrossTable(SalesmanId, [%Commission], 3)
LOAD * INLINE 
[
CompanyCode,FamilyCode,FamilyDescription,SalesmanId4,SalesManId5,SalesManId12,SalesManId14
1,ANIM,XXX,10%,5%,10%,10%
1,APPR,YYY,5%,10%,5%,10%
1,AV,ZZZ,10%,10%,10%,5%
](delimiter is ',');
 
NewTable:
Load FamilyCode,
Mid(SalesmanId, 11) as SalesmanId,
    [%Commission]
    Resident Table;
marcel_olmo
Partner Ambassador
Partner Ambassador
Author

Thanks @Lisa_P for the try! It's very close of what I need.

In my real case, there's no SalesMainId4, there are real names like John,James, etc.. so your code still doesn't work. 😞

It's something like this :

        4 5 12 14
CompanyCode FamilyCode SubFamilyCode FamilyDescription Mary David John James
1 ANIM ********** XXX 10% 5% 10% 10%
1 APPR ********** YYY 5% 10% 5% 10%
1 AV ********** ZZZ 10% 10% 10% 5%

I've uploaded a new sample file to understand what I mean.

Kind regards, Marcel.

 

musketeers
Creator
Creator

Hi, Have you tried Generic Load in qliksense..Not sure if this work in your case.

rodrigo_martins
Partner - Creator
Partner - Creator

Hi,

Tried this script with your sample, worked for me:

Table1:
CrossTable(SalesMan, [%Commission], 4)
LOAD
    *
FROM [lib://Dados/Sales Comission Example - v2.xlsx]
(ooxml, embedded labels, header is 1 lines, table is InputTable);

Table2:
CrossTable(SalesManId, SalesMan, 1)
LOAD
    1 as _temp,
    *;
Load
*
FROM [lib://Dados/Sales Comission Example - v2.xlsx]
(ooxml, embedded labels, table is InputTable)
WHERE F1 = 'CompanyCode';

Left Join(Table1)
Load * Resident Table2;

Drop Field _temp;
Drop Table Table2;

rodrigo_martins_0-1715098963305.png

 

marcel_olmo
Partner Ambassador
Partner Ambassador
Author

Thank you @rodrigo_martins that was what I needed! Good job 😄