topic Sum up values based on dimension during load??? in QlikView

Sum up values based on dimension during load???

poluvidyasagar — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to achieve the following. Can someone explain how do i load the following with the result table shown below:

Dataset1:
PN	Day1	Day2:	Day3:
1234	3	4	5
2345	10	12	10
3456	9	8	7
5678	6	8	8
9876	5	6	8

Dataset2:
PN	Day1	Day2:	Day3:
9981	3	4	5
6671	10	12	10
5674	9	8	7
1234	10	10	10
2345	20	20	20

Result:
PN	Day1	Day2:	Day3:
1234	13	14	15
2345	30	32	30
3456	9	8	7
5678	6	8	8
9876	5	6	8
9981	3	4	5
6671	10	12	10
5674	9	8	7

I tried to use the concatenate/Join, but it does give me the parts repeated twice in both tables.

Can someone help?

Thanks,
Vidya

Re: Sum up values based on dimension during load???

vamsee — Tue, 18 Sep 2018 15:57:17 GMT

Try

Dataset1:
LOAD * INLINE [
PN, Day1, Day2, Day3
1234, 3, 4, 5
2345, 10, 12, 10
3456, 9, 8, 7
5678, 6, 8, 8
9876, 5, 6, 8
];
Concatenate
Dataset2:
LOAD * INLINE [
PN, Day1, Day2, Day3
9981, 3, 4, 5
6671, 10, 12, 10
5674, 9, 8, 7
1234, 10, 10, 10
2345, 20, 20, 20

];
Result:
NoConcatenate
LOAD
PN,
SUM(Day1) as Day1,
SUM(Day2) as Day2,
SUM(Day3) as Day3
Resident Dataset1
Group by PN;
DROP Table Dataset1;

Re: Sum up values based on dimension during load???

Anonymous — Tue, 18 Sep 2018 15:57:52 GMT

Hi,

Do you want to do it in the script or on the UI ?

I think it is better to do it in the UI, you just have to create a table in the graphs, then put PN and Days as dimension, the expression should be

sum(Day1)+sum(Day2)+sum(Day3)

Re: Sum up values based on dimension during load???

poluvidyasagar — Tue, 18 Sep 2018 16:06:13 GMT

I want to do it in loading script.

Re: Sum up values based on dimension during load???

vishsaggi — Tue, 18 Sep 2018 16:06:50 GMT

Try this:

Dataset1:

LOAD * INLINE [

PN, Day1, Day2, Day3

1234, 3, 4, 5

2345, 10, 12, 10

3456, 9, 8, 7

5678, 6, 8, 8

9876, 5, 6, 8

];

Dataset2:

LOAD * INLINE [

PN, Day1, Day2, Day3

9981, 3, 4, 5

6671, 10, 12, 10

5674, 9, 8, 7

1234, 10, 10, 10

2345, 20, 20, 20

];

NoConcatenate

Final:

LOAD PN,

Sum(Day1) AS Day1,

Sum(Day2) AS Day2,

Sum(Day3) AS Day3

Resident Dataset1

Group By PN;

Drop Table Dataset1;

Re: Sum up values based on dimension during load???

Anonymous — Tue, 18 Sep 2018 16:09:20 GMT

Then when 2 tables have the same fields, QlikView automatically concatenate the 2 tables.

So you just have to create a new table in which you use sum(DAYS_FIELD) with a group by PN (dont forget to use NO CONCATENATE) otherwise this new table will be added concatenate to the old one also.

Re: Sum up values based on dimension during load???

poluvidyasagar — Thu, 20 Sep 2018 12:24:18 GMT

Thanks Vamsee. It worked

Re: Sum up values based on dimension during load???

poluvidyasagar — Thu, 20 Sep 2018 12:24:51 GMT

Thanks Vishwa. It worked.