Set analysis problem

[Arianna1]

Hello,

Please I need your help, I have a table: 

tab:
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
]

i want to show a new table (using set analysis or if clause):

Totals          sum(unitprice)=77 , 6

Astrida, aa,                 31        ,1

Astrida,bb,                   9         ,1

betacap,bb,                22       ,1

betacap,cc,                 15       ,1

where the new column is contains 1 for each combination of (customer,product) and 6 as total of records from table tab.

 Thank you

Arianna

[Frank_Hartmann]

script:

tab:
load *, AutoNumber(customer&product) as AN;
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
];

straighttable:

dim1: customer

dim2: product

exp1: sum(unitprice)

exp2: count(AN)

[Arianna1]

Thank you Frank but if i follow you I would have:

customer	product	sum(unitprice)	count(AN)
Totali		77	6
astrida	aa	31	2
astrida	bb	9	1
betacap	bb	22	2
betacap	cc	15	1

the new column has two records (astrida,aa,31,2) and (betacap,bb,22,2) but I would have

(astrida,aa,31,1) and (betacap,bb,22,1).

[Frank_Hartmann · Accepted solution]

script:

island:
Load * Inline [
dim
1
2
];

tab:
load *, AutoNumber(customer&product) as AN,
(customer&product) as key;
load* inline [ customer,product, unitprice
astrida,aa,15
astrida,aa,16
astrida,bb,9
betacap,bb,10
betacap,bb,12
betacap,cc,15
];

straighttable:

dim:     pick(dim,key,'Total')

dim_ background_color:     if(pick(dim,key,'Total')='Total',LightGray(),)

exp1:    sum(unitprice)

exp1_background_color:    if(pick(dim,key,'Total')='Total',LightGray(),)

exp2:    if(pick(dim,key,'Total')='Total',count(AN),count(DISTINCT AN))

exp2_background_color:    if(pick(dim,key,'Total')='Total',LightGray(),)

[Arianna1]

Thank for your help and your kindness.

Arianna