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: 
beck_bakytbek
Master
Master

sum in generic load

Hi Folks,

i have a situation , i am using this code:

Table:

LOAD * INLINE [

Product, Month, Sales

A, Jan 2017, 10

A, Mar 2017, 20

A, Apr 2017, 30

A, Apr 2017, 1

B, Apr 2017, 2

B, Apr 2017, 3

B, Mai 2017, 40

B, Jun 2017, 50

];

FinalTable:

LOAD DISTINCT Product

Resident Table;

For i = 1 to FieldValueCount('Month')

LET vField = FieldValue('Month', $(i));

Left Join (FinalTable)

LOAD DISTINCT Product,

Sales as [$(vField)]

Resident Table

Where Month = '$(vField)';

Next

DROP Table Table;

My Problem (see attached screenshot) and Question is: how can i show with generic load the sum: 31 in Column Apr 2017 for Product A and Sum:5 for Product B with my Column: Apr 2017, as you see, the table generates a row and the same time my table produces the duplicates for another dimensions.

Thanks a lot

Beck

1 Solution

Accepted Solutions
sunny_talwar

May be use the generic field as expressions

Sum([Apr 2017])

Capture.PNG

View solution in original post

5 Replies
CarlosAMonroy
Creator III
Creator III

Hi Beck,

You can try the following:

Table:

LOAD * INLINE [

Product, Month, Sales

A, Jan 2017, 10

A, Mar 2017, 20

A, Apr 2017, 30

A, Apr 2017, 1

B, Apr 2017, 2

B, Apr 2017, 3

B, Mai 2017, 40

B, Jun 2017, 50

];

NoConcatenate

FinalTable:

LOAD DISTINCT Product, Month, sum(Sales) as Sales

Resident Table

group by Product, Month;

drop table Table;


Highlighted in blue is the key to achieve what you want.


Will show this result:


6-14-2017 8-43-27 AM.png

Thanks,

Carlos

beck_bakytbek
Master
Master
Author

Hi Carlos,

it is a nice approach, but i want to know, how can i show my sum on the base of generic load

Thanks  a lot

Beck

CarlosAMonroy
Creator III
Creator III

sunny_talwar

May be use the generic field as expressions

Sum([Apr 2017])

Capture.PNG

beck_bakytbek
Master
Master
Author

Sunny, Carlos

thanks a lot for your feedback and help

Beck