Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

fgiorgio
Contributor

Load 1 instance of a Field and Sum corresponding multiple values

Hi all,

I have a table with  multiple Sales Order Lines for various sales orders.

so my table looks like this:

SOrder |    SO Line #     | SOrderLine Value

A          |             1          |         10

A          |             2          |         10

A          |             3          |         15

A          |             4          |         10

B          |             1          |          5

B          |             2          |         20

B          |             3          |         10

What I want to end up with is:

SOrder     SO Total

A          |          45

B          |          35

I can do a SUM(SOLineValue) as SOTotal Resident TableX Group By SOrderNo

but how can I Load only 1 instance of the SOrder in my Totals Table?

not sure if this makes sense...

Reason for this, is that when i load from an SOrderline table, I get the Sorder repeated as many times as there are SOrder Lines, and this appears to be messing up some calculations that are made per SOrder, not SOrderline...

please let me know your thoughts...

thanks...

F.Giorgio

1 Solution

Accepted Solutions
Highlighted
antoniotiman
Honored Contributor III

Re: Load 1 instance of a Field and Sum corresponding multiple values

Hi Fabrizio,

try like this

LOAD SOrder,Sum(SOrderLineValue) as TotalValue Inline [
SOrder | SO Line # | SOrderLineValue
A | 1 | 10
A | 2 | 10
A | 3 | 15
A | 4 | 10
B | 1 | 5
B | 2 | 20
B | 3 | 10
]
(delimiter is '|')
Group By SOrder
;

Regards,

Antonio

6 Replies

Re: Load 1 instance of a Field and Sum corresponding multiple values

May be this?

Directory:

LOAD * Inline [

SOrder ,    SO Line #     , SOrderLine Value

A          ,             1          ,         10

A          ,             2          ,         10

A          ,             3          ,         15

A          ,             4          ,         10

B          ,             1          ,          5

B          ,             2          ,         20

B          ,             3          ,         10

];

LOAD SOrder, Sum([SOrderLine Value]) as [SO Total] Resident Directory Group By SOrder;

DROP Table Directory;

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)
fgiorgio
Contributor

Re: Load 1 instance of a Field and Sum corresponding multiple values

Thanks Anil,

However, that would give me:

SOrder     So Total

A          |             45        |        

A          |             45        |        

A          |             45        |        

A          |             45        |        

B          |             35        |         

B          |             35        |      

B          |             35        |     

Which I am trying to avoid...

I need to simplify to:

SOrder     SO Total

A          |          45

B          |          35

Highlighted
antoniotiman
Honored Contributor III

Re: Load 1 instance of a Field and Sum corresponding multiple values

Hi Fabrizio,

try like this

LOAD SOrder,Sum(SOrderLineValue) as TotalValue Inline [
SOrder | SO Line # | SOrderLineValue
A | 1 | 10
A | 2 | 10
A | 3 | 15
A | 4 | 10
B | 1 | 5
B | 2 | 20
B | 3 | 10
]
(delimiter is '|')
Group By SOrder
;

Regards,

Antonio

Re: Load 1 instance of a Field and Sum corresponding multiple values

Not sure, I followed you now. Can you check the mean of Simply from antonio Reply

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)
fgiorgio
Contributor

Re: Load 1 instance of a Field and Sum corresponding multiple values

Sorry guys,

i've just realised that adding the deposit field (per Sorder) to this table is causing the below:

SOrder     So Total

A          |             45        |       

A          |             45        |       

A          |             45        |       

A          |             45        |       

B          |             35        |        

B          |             35        |     

B          |             35        |  

I need to look further into the deposit field and see how I'm going to import it into the table without creating multiple lines (as the original source table has SOrderlines on it, if I import from there it will repeat by the number of SOrder lines...)... the SOTotals part below is working fine...

SOCSOBalances:

LOAD

SOCSOrderNo,

Sum(SOCSOLineTotalincDisc) as SOCSOGrandTotal Resident SOCMaster Group By SOCSOrderNo;

I will leave this open for a couple of days and will come back to it if I have more questions...

thanks for your input though...

cheers Fab

Re: Load 1 instance of a Field and Sum corresponding multiple values

Sure, You can mark it as answered correct and helpful to close this thread. If you want to reopen the Thread your wish with new issue

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)