Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Group By in Load script doesnt work

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
Highlighted
Partner
Partner

Re: Group By in Load script doesnt work

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

Highlighted

Re: Group By in Load script doesnt work

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)
Highlighted
MVP
MVP

Re: Group By in Load script doesnt work

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; 

Highlighted
Contributor III
Contributor III

Re: Group By in Load script doesnt work

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

Highlighted
Contributor III
Contributor III

Re: Group By in Load script doesnt work

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

Highlighted

Re: Group By in Load script doesnt work

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)