two for..next and one while..having with firstsortedvalue for a basket analysis
Sergio Alonso Bueno Jun 10, 2012 5:53 PMHi everyone,
I need some help to solve the next requirement. I have the Fact aggregate table with fields:
: yearmonth / market / customer / product / sumSales
For a defined set of the products I need to categorize and classify each and all of the customers sumsales , as what their choice was.
Need to create for each customer adicional dimension FirstChoice, SecondChoice,Nochoice but at the SCRIPT LEVEL.
MONTH_YEAR  MARKET  custome  PRODUCT  MTH_QTY 
201201  vegtables  108385  onion  5 
201201  vegtables  108385  carrot  2 
201201  vegtables  108393  tomato  4 
201201  vegtables  108393  carrot  3 
201201  vegtables  108393  iogurt  1 
201201  vegtables  108365  onion  2 
201201  vegtables  108365  carrot  6 
201201  vegtables  108364  iogurt  1 
For onion and carrots I need to obtain the next data
Customer / yearmonth / market/ FirstChoice/ SecondChoice/Nochoice, for the example data
108385 /201201/ vegetables /onion/carrot/null
108393 /201201/ vegetables / tomato/ carrot/ null
108365 / 201201/ vegetables / carrot/ onion/ null
108364 / 201201 / vegetables / iogurt/null/ 1
Quite complex situation. Any suggestion…?.
I´ve load on script rank for the consumer but need to run through all the table customer by customer to calculate the new dimensions. Ideas are welcome.
Thank you very much.
El mensaje fue editado por: SergioABR Thanks Stephan with your ideas... i´ve arrived to something like..
//MAPING LOAD MAP_PROD: MAPPING LOAD * INLINE [ PRODUCT,FLAG CARROT,Y ONIONS,Y ORANGE,N PATATOES,Y TOMATO,N ];
PRODUCT: LOAD subfield(K_CUST_PROD,'',2) AS PRODUCT FROM [..\vEGETABLES bASKET.csv] (ansi, txt, delimiter is ';', embedded labels, msq); CUSTOMER: MAPPING LOAD subfield(K_CUST_PROD,'',1) as CUSTOMER FROM [..\vEGETABLES bASKET.csv] (ansi, txt, delimiter is ';', embedded labels, msq);
let TotCustomer = noofrows(CUSTOMER)1;
FOR i= 0 TO TotCustomer
LET vCUSTOMER = PEEK(CUSTOMER,$(i),'CUSTOMER') ;
let TotPROD = noofrows(PRODUCT)1;
FOR j= 0 TO TotPROD
LET VPROD = PEEK(PRODUCT,$(i),'PRODUCT') ; CUST_POS:
LOAD PROD&vCUSTOMER&ITERNO()) AS KEY
,FIRSTSORTEDVALUE(PRODUCT,RANK,1) AS TOP1PROD
,FIRSTSORTEDVALUE(RX_MTH_QTY,RANK,1 AS TOP1VALUE
,IF((FIRSTSORTEDVALUE(PRODUCT,RANK,1))= $(VPROD),'1',
IF((FIRSTSORTEDVALUE(PRODUCT,RANK,2))= $(VPROD),'2','NA')) AS PRODPOS , IF (SUM(RX_MTH_QTY) > 0,'DEAL','NODEAL') AS STATUS GROUP BY CUSTOMER,PROD
HAVING WILDMATCH (PRODUCT,APPLYMAP('MAP_PROD',PRODUCT),'Y'>0 ;
NEXT NEXT WHILE ITERNO() >=10 AND ITERNO()<=12;
BUT IS NOT WORKING............ I HAVE POSTED TWO FILES. ONE IS THE QVW FILE AND A *.CSV FILE WITH DATA.
Thank you

vEGETABLES bASKET.csv 76.6 K

bASKET_VEG.qvw 183.8 K