Measure divided between (team of) two dimensions / columns

[frankycardinale]

Hello

I have an input table with two dimensions <am1 and am2> and one measure <sales>, like the following:

am1	am2	sales
pluto		7
pluto	pippo	4
pluto	topolino	10
pippo		1
pippo	pluto	6
pippo	topolino	2
topolino		5
topolino	pluto	4
Total		39

I would like to get an output table, in the app, like the following:

am	am team	sales	sales by am
pluto	pluto	7	7
pluto	pluto - pippo	4	2
pluto	pluto - topolino	10	5
pluto	pippo - pluto	6	3
pluto	topolino - pluto	4	2
pippo	pippo	1	1
pippo	pippo - pluto	6	3
pippo	pippo - topolino	2	1
pippo	pluto - pippo	4	2
topolino	topolino	5	5
topolino	topolino - pluto	4	2
topolino	pluto - topolino	10	5
topolino	pippo - topolino	2	1
Totale			39

In wich I have:

- am: all am (am1 or am2)

- am team: am1 + am2

- sales: sales of 'am team'

- sales by am: if 'am team' is composed of 1 am -> sales, if 'am team' is composed of 2 am -> sales/2

how can I get this result?

Thanks

EDIT 30/08/2018.

The input table is actually just an extraction I posted as an example. It belongs to a complex snow-flake scheme defined in Qlik.
am1 and am2 are fields of two dimension tables, sales is the field of a fact table. The fact table is connected to the two dimensions via id fields.

[petter]

Here is a load script that will transform your input into the output you need:

T1: 
LOAD
  If( IterNo()=1, am1 , am2 ) AS am,
  am1 & If( ams=2 , '-' & am2) AS [am team],
  sales,
  If( ams=2 , sales/2 , sales ) AS [sales by am]
WHILE 
  IterNo()<=ams;
LOAD 
  am1, 
  am2, 
  sales,
  If( am2 = '' , 1 , 2 ) AS ams
INLINE [
am1,am2,sales
pluto,,7
pluto,pippo,4
pluto,topolino,10
pippo,,1
pippo,pluto,6
pippo,topolino,2
topolino,,5
topolino,pluto,4
];

[acardella]

Hi Francesco,

you can try with this...

bye

Script
LOAD *, SubField([am team],' - ') as am1 ; LOAD am1& if(Len(am2)>0, ' - ' & am2) as [am team], sales, if(Len(am2)>0,sales/2,sales) as [sales by am] ; LOAD * INLINE [     am1, am2, sales     pluto, , 7     pluto, pippo, 4     pluto, topolino, 10     pippo, , 1     pippo, pluto, 6     pippo, topolino, 2     topolino, , 5     topolino, pluto, 4 ];

[frankycardinale]

Sorry, I have edited the answer.

I cannot use your script because my input table is a complex snow-flake scheme.

Thanks a lot for your solution.

[frankycardinale]

Sorry, I have edited the answer.

I cannot use your script because my input table is a complex snow-flake scheme.

Thanks a lot for your solution.