Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
andyrebs
Contributor III
Contributor III

Load a table with in the script with aggregated data

Hi!

I need to load in my script a table with aggregated data.

In my database I have a table with these data :

 
 

andyrebs_4-1672234258145.png

I need to load the data in a single row grouped by N°MovMag and Articolo, I need the sum of Var ordineMovMag.

How I need to load the data to get this result?

andyrebs_5-1672234692470.png

 

 Many thanks

 

Andrea

QlikView 

Labels (2)
1 Solution

Accepted Solutions
Taoufiq_Zarra

@andyrebs 

in load script you can use group by

for example suppose you have this input :

A B C D E
1 2 214 10 14
1 2 215 11 15
1 2 216 12 16
2 2 217 13 17
2 1 218 14 18
2 5 219 15 19
2 6 220 16 20
5 2 221 17 21
6 1 222 18 22
4 1 223 19 23
7 2 224 20 24
8 2 225 21 25
8 2 226 22 26
9 1 227 23 27

 

and you want to sum(C) with group by A and B

you can use this script:

DATA:
LOAD * INLINE [
    A, B, C, D, E
    1, 2, 214, 10, 14
    1, 2, 215, 11, 15
    1, 2, 216, 12, 16
    2, 2, 217, 13, 17
    2, 1, 218, 14, 18
    2, 5, 219, 15, 19
    2, 6, 220, 16, 20
    5, 2, 221, 17, 21
    6, 1, 222, 18, 22
    4, 1, 223, 19, 23
    7, 2, 224, 20, 24
    8, 2, 225, 21, 25
    8, 2, 226, 22, 26
    9, 1, 227, 23, 27
];

output:

load A,B, sum(C) as Total resident DATA group by A,B; 

drop table DATA;

 

output:

Taoufiq_Zarra_0-1672235630316.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

3 Replies
Taoufiq_Zarra

@andyrebs 

in load script you can use group by

for example suppose you have this input :

A B C D E
1 2 214 10 14
1 2 215 11 15
1 2 216 12 16
2 2 217 13 17
2 1 218 14 18
2 5 219 15 19
2 6 220 16 20
5 2 221 17 21
6 1 222 18 22
4 1 223 19 23
7 2 224 20 24
8 2 225 21 25
8 2 226 22 26
9 1 227 23 27

 

and you want to sum(C) with group by A and B

you can use this script:

DATA:
LOAD * INLINE [
    A, B, C, D, E
    1, 2, 214, 10, 14
    1, 2, 215, 11, 15
    1, 2, 216, 12, 16
    2, 2, 217, 13, 17
    2, 1, 218, 14, 18
    2, 5, 219, 15, 19
    2, 6, 220, 16, 20
    5, 2, 221, 17, 21
    6, 1, 222, 18, 22
    4, 1, 223, 19, 23
    7, 2, 224, 20, 24
    8, 2, 225, 21, 25
    8, 2, 226, 22, 26
    9, 1, 227, 23, 27
];

output:

load A,B, sum(C) as Total resident DATA group by A,B; 

drop table DATA;

 

output:

Taoufiq_Zarra_0-1672235630316.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
andyrebs
Contributor III
Contributor III
Author

Hi Taoufiq

Many thanks for your prompt reply, I followed your suggestion but when I load the script, no table is created. 

I list below the script

MovMagSDDT:
SQL select
SJU.VARORD_0 as [Var ordineMovMag],
SJU.VCRNUM_0 + STR(SJU.VCRLIN_0) as _KeyConsegna
FROM xxx.YYYYY.NNNNN SJU
;

MovMagCopia:
Load
sum([Var ordineMovMag]) as [Var ordineMovMag],
_KeyConsegna

Resident MovMagSDDT
group by _KeyConsegna
;
drop table MovMagSDDT;

 

Where do I wrong?

 

Thanks

andyrebs
Contributor III
Contributor III
Author

Solved, I just added a field in the dropped table. It works now. 

Thanks again.

 

Best regards

 

Andrea