Group By in Load script doesnt work

[janturon]

Hello,

i have code like this

SOURCE:
LOAD 
     DATUM as %Datum, 
     PRED as %Pred,
     If(Match(CPOH,'0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0501','0502','0503','0504','0505','0506','0507','0508','0509','0510'),'P','N') as @To, 
     ApplyMap('MAP_TS03',REG,NULL()) as Ts03,
     ApplyMap('MAP_TS04',REG,NULL()) as Ts04,
     ApplyMap('MAP_TS05',REG,NULL()) as Ts05,
     ApplyMap('MAP_TS06',REG,NULL()) as Ts06,
     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),SUMPNC*-1,SUMPNC) as #SPnc,
     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),(SUMPPC-ZLAVA-ZLAVA_C)*-1,(SUMPPC-ZLAVA-ZLAVA_C)) as #SMot,
     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),((SUMPPC-ZLAVA-ZLAVA_C)/((SDPH/100)+1))*-1,(SUMPPC-ZLAVA-ZLAVA_C)/((SDPH/100)+1)) as #SMotBd,
     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),ZLAVA*-1,ZLAVA) as #SZla,
     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),ZLAVA_C*-1,ZLAVA_C) as #SZlaC,
     if((SKU)='415','AXA', 
        if((SKU)='416','NBL', 
            if((SKU)='417','NIKE',Null() ))) as @Sluz,     
     TYPZC as Typzc,
     TYPZP as Typzp,
     TYPZLA as TypZla 
FROM
(qvd) where MATCH(CPOH,'0101','0102','0103','0104','0106','0201','0202','0206','0204','0401','0402','0403','0404',
'0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0501','0502','0503','0504','0505','0506','0507','0508','0509','0510')
and DATUM>='01-01-2017' and DATUM<='31-01-2017';

PM:
LOAD
     %Datum, 
     %Pred,
     @To, 
     Ts03,
     Ts04,
     Ts05,
     Ts06,
     Typzc,
     Typzp,
     TypZla,
     @Sluz,
     Sum(#SPnc)as #SPnc,
     Sum(#SMot) as #SMot,
     Sum(#SMotBd)as #SMotBd,
     Sum(#SZla) as #SZla,
     Sum(#SZlaC) as #SZlaC
Resident SOURCE
Group by %Datum, %Pred, @To, Ts03, Ts04, Ts05, Ts06, Typzc, Typzp, TypZla, @Sluz;

DROP Table SOURCE;

Group by function doesnt work. When script is reloading I see data from table SOURCE are loaded and then data from PM are appended to SOURCE (not cumulated) ... after reload ends, model is without any data and table... Pls help. THX

[RonaldDoes · Accepted solution]

Hi Jan,

Use NoConcatenate LOAD for your PM-table.

Because the fields are the same, QV will autoconcatenate it to SOURCE and then DROP your only table at the end of the script.

Hope this helps you.

With kind regards,

Ronald

[Anil_Babu_Samineni]

Could be the reason here

1) Remove the DROP Statement and reload

2) Use NoConcatenate for Resident Directory

1. PM: 
2. NoConcatenate
   
3. LOAD 
4.      %Datum,  
5.      %Pred, 

[Kushal_Chawda]

SOURCE: 

LOAD  

     DATUM as %Datum,  

     PRED as %Pred, 

     If(Match(CPOH,'0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0501','0502','0503','0504','0505','0506','0507','0508','0509','0510'),'P','N') as @To,  

     ApplyMap('MAP_TS03',REG,NULL()) as Ts03, 

     ApplyMap('MAP_TS04',REG,NULL()) as Ts04, 

     ApplyMap('MAP_TS05',REG,NULL()) as Ts05, 

     ApplyMap('MAP_TS06',REG,NULL()) as Ts06, 

     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),SUMPNC*-1,SUMPNC) as #SPnc, 

     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),(SUMPPC-ZLAVA-ZLAVA_C)*-1,(SUMPPC-ZLAVA-ZLAVA_C)) as #SMot, 

     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),((SUMPPC-ZLAVA-ZLAVA_C)/((SDPH/100)+1))*-1,(SUMPPC-ZLAVA-ZLAVA_C)/((SDPH/100)+1)) as #SMotBd, 

     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),ZLAVA*-1,ZLAVA) as #SZla, 

     if(match(CPOH,'0101','0102','0103','0104','0106','0204','0401','0402','0403','0404','0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0520','0522'),ZLAVA_C*-1,ZLAVA_C) as #SZlaC, 

     if((SKU)='415','AXA',  

        if((SKU)='416','NBL',  

            if((SKU)='417','NIKE',Null() ))) as @Sluz,      

     TYPZC as Typzc, 

     TYPZP as Typzp, 

     TYPZLA as TypZla  

FROM 

(qvd) where MATCH(CPOH,'0101','0102','0103','0104','0106','0201','0202','0206','0204','0401','0402','0403','0404', 

'0405','0406','0407','0408','0409','0410','0414','0415','0416','0417','0418','0419','0501','0502','0503','0504','0505','0506','0507','0508','0509','0510') 

and DATUM>='01-01-2017' and DATUM<='31-01-2017'; 

PM: 

NOCONCATENATE

LOAD 

     %Datum,  

     %Pred, 

     @To,  

     Ts03, 

     Ts04, 

     Ts05, 

     Ts06, 

     Typzc, 

     Typzp, 

     TypZla, 

     @Sluz, 

     Sum(#SPnc)as #SPnc, 

     Sum(#SMot) as #SMot, 

     Sum(#SMotBd)as #SMotBd, 

     Sum(#SZla) as #SZla, 

     Sum(#SZlaC) as #SZlaC 

Resident SOURCE 

Group by %Datum, %Pred, @To, Ts03, Ts04, Ts05, Ts06, Typzc, Typzp, TypZla, @Sluz; 

DROP Table SOURCE; 

[janturon]

thank you very much. it works. I forgot of concatenation... 🙂

[janturon]

thank you very much. it works. I forgot of concatenation... 🙂

[Anil_Babu_Samineni]

NP, Close this thread by flag of Ronald answer as Correct answer