Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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(),)
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)
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).
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(),)
Thank for your help and your kindness.
Arianna