Crosstable Challenge. SalesMan Comissions by Product Group

[marcel_olmo]

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.

[Lisa_P]

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]

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]

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

[rodrigo_martins · Accepted solution]

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;

[marcel_olmo]

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