Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mshailaja
Contributor III
Contributor III

need help

Hi

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?

1 Solution

Accepted Solutions
adamdavi3s
Master
Master

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.

View solution in original post

12 Replies
avinashelite

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

Anil_Babu_Samineni

Can you post application,

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable

Group by should be used when you are doing tranformation.

puttemans
Specialist
Specialist

What do you sum on? Id?

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

adamdavi3s
Master
Master

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

Anonymous
Not applicable

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

E.g.

Load

sum(sales) as  Sales,

id

group by id;

adamdavi3s
Master
Master

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.

puttemans
Specialist
Specialist

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,