topic Re: need help in QlikView

need help

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

I am having table coming from the database and loading into the qvd and storing as extractor qvw, this extractor qvw ( where qvd resides) will use for some other applications. so i dont want to make calculations on this table .

the data in the qvd looks like this

Load * inline

[

Duration , id

1,A1

2,D2

3,F5

4,H6

5,J8

.........

567546,UJHY6

];

Now i have to do 3 claculations on backend script level

the 3 calculations are

1)Duration/60 as Durations

2)Sum(Durations) as correctduration

3)(if(correctduration <60,correctduration ,correctduration /60)) as Right Duration

Load2;
LOAD
(if(correctduration <60,correctduration ,correctduration /60)) as Right Duration , id;
Load1:
LOAD
Sum(Durations) as correctduration , id;
LOAD:
LOAD
Duration/60 as Durations ,
id;

Load * inline

[

Duration , id

1,A1

2,D2

3,F5

4,H6

5,J8

.........

567546,UJHY6

];

While i am trying in the resident load , i am getting the output, but the reload time is very high and it is impacting the performance

So i went for preceding lload like the above script, i am getting the errors.

My question is we can do multiple precedng loads in the same load statement if means can any one give me the solution for the above?

Re: need help

avinashelite — Tue, 27 Sep 2016 11:15:55 GMT

I don't think we can use the sum() directly on the preceding load ...

Re: need help

Anil_Babu_Samineni — Tue, 27 Sep 2016 11:17:30 GMT

Can you post application,

Re: need help

Anonymous — Tue, 27 Sep 2016 11:21:35 GMT

Group by should be used when you are doing tranformation.

Re: need help

puttemans — Tue, 27 Sep 2016 11:27:41 GMT

What do you sum on? Id?

You need a group by anyway, and that won't fit a preceding load.

Re: need help

varshavig12 — Tue, 27 Sep 2016 11:40:19 GMT

Re: Can there be a SUM() function in the Preceding Load? It is not working for me. If not, why is that not allowed?

Re: need help

adamdavi3s — Tue, 27 Sep 2016 11:44:10 GMT

You can do but you must have a group by, the same as SQL really

Re: need help

Anonymous — Tue, 27 Sep 2016 11:48:41 GMT

Wherever you want to apply any aggregation function you have to use Group By clause.

E.g.

Load

sum(sales) as Sales,

group by id;

Re: need help

adamdavi3s — Tue, 27 Sep 2016 11:55:12 GMT

I believe this is what you are looking for?

LOAD id,

sum(if(correctduration <60,correctduration ,correctduration /60)) as Right_Duration

GROUP BY id;

LOAD id,

sum(Durations) as correctduration

GROUP BY id;

LOAD id,

sum(Duration/60) as Durations

GROUP BY id;

Load * inline

[

Duration , id

1,A1

2,D2

3,F5

4,H6

5,J8

13156456,A5

];

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: need help

puttemans — Tue, 27 Sep 2016 11:58:45 GMT

Apparently a group by in a preceding load is possible, then it would look like :

tablename:
LOAD
if(correctduration <60,correctduration ,correctduration /60) as Right Duration,

id;

LOAD
id,

Sum(Durations) as correctduration

Group by id;

LOAD
Duration/60 as Durations ,
id;

Load * inline

[

Duration , id

1,A1

2,D2

3,F5

4,H6

5,J8

.........

567546,UJHY6

];

Regards,

Re: need help

avinashelite — Tue, 27 Sep 2016 11:59:18 GMT

Yes adamdavi3s‌ that s what is was trying to mention we cannot directly use it we would need the Group by clause statement

Re: need help

mshailaja — Wed, 28 Sep 2016 05:12:25 GMT

@ Johan , Thanks for the Solution

Re: need help

puttemans — Wed, 28 Sep 2016 06:28:30 GMT

Good morning Sailaja,

Glad I could help you with your question. Could you please mark my answer as the correct one, and by doing so close this thead?

Kind regards,

Johan