Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Group By in Load script doesnt work

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

1 Solution

Accepted Solutions
Partner
Partner

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

View solution in original post

6 Replies
Partner
Partner

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

View solution in original post

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, 
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

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; 

Contributor III
Contributor III

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

Contributor III
Contributor III

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)