topic Re: Group By in Load script doesnt work in QlikView

Group By in Load script doesnt work

janturon — Tue, 20 Jun 2017 06:59:38 GMT

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

Re: Group By in Load script doesnt work

RonaldDoes — Tue, 20 Jun 2017 07:03:22 GMT

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

Re: Group By in Load script doesnt work

Anil_Babu_Samineni — Tue, 20 Jun 2017 07:03:25 GMT

Could be the reason here

1) Remove the DROP Statement and reload

2) Use NoConcatenate for Resident Directory

PM:
NoConcatenate
LOAD
%Datum,
%Pred,

Re: Group By in Load script doesnt work

Kushal_Chawda — Tue, 20 Jun 2017 07:11:34 GMT

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((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;

Re: Group By in Load script doesnt work

janturon — Tue, 20 Jun 2017 07:13:21 GMT

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

Re: Group By in Load script doesnt work

janturon — Tue, 20 Jun 2017 07:13:37 GMT

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

Re: Group By in Load script doesnt work

Anil_Babu_Samineni — Tue, 20 Jun 2017 07:14:54 GMT

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