topic Re: Load a table with in the script with aggregated data in QlikView

Load a table with in the script with aggregated data

andyrebs — Wed, 28 Dec 2022 13:41:05 GMT

Hi!

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

In my database I have a table with these data :

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?

Many thanks

Andrea

Re: Load a table with in the script with aggregated data

Taoufiq_Zarra — Wed, 28 Dec 2022 13:53:57 GMT

@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:

Re: Load a table with in the script with aggregated data

andyrebs — Wed, 28 Dec 2022 15:44:08 GMT

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

Re: Load a table with in the script with aggregated data

andyrebs — Wed, 28 Dec 2022 16:18:54 GMT

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

Thanks again.

Best regards

Andrea

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

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

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