topic Re: Group By multiple fields in QlikView

Group By multiple fields

Mon, 01 Feb 2016 22:49:20 GMT

Hi all,

I have source data which has multiple lines for the one primary key.

It goes like this

table EDW:

Property Reference | SOR Code | Work Programme Bk | Location | Total Cost

1 kit01 LCQ kitchen 100

1 kit03 LCQ kitchen 120

1 kit04 PR3 kitchen 140

1 pgi01 LCQ bedroom 95

1 pgi03 LCQ bedroom 150

1 plu40 SOW bath 400

2 kit50 LCQ kitchen 200

2 plu05 LCQ bath 400

2 ele30 LCQ WC 350

2 min95 RSW HALL 40

what i'm trying to do now is figure out the sum of the total cost of all LCQ items for anything starting with 'kit'

the following is what I have written so far, but the sum works out to be something completely different.

I would really appreciate a little guidance

LOAD

[Property Reference],

SUM(IF(([Total Cost] > 0) AND ([SOR Code] like 'KIT*) AND ([Work Programme Bk] like 'LCQ'), [Total Cost], 0] as KIT_Total_LCQ_Cost

Resident EDW

Group by [Property Reference] and [Work Programme Bk] and [SOR Code];

When I run this function I keep getting that its an "invalid expression"

Could someone shed a bit of light on where I might be wrong?

Regards,

Yomna

Re: Group By multiple fields

Mon, 01 Feb 2016 23:04:11 GMT

what I also forgot to mention is the fact that when I remove the last 2 fields in the group by line, and just say

group by [Property Reference] ;

this is where I get a totally different sum to what I expected.

Re: Group By multiple fields

swuehl — Mon, 01 Feb 2016 23:14:16 GMT

If you want to group your sum by Property Reference, try

LOAD

[Property Reference],

SUM( [Total Cost] ) as KIT_Total_LCQ_Cost

Resident EDW

WHERE [Work Programme Bk] = 'LCQ' AND [SOR Code] LIKE 'kit*' AND [Total Cost] > 0

Group by [Property Reference];

Re: Group By multiple fields

Tue, 02 Feb 2016 00:50:40 GMT

Unfortunately this still doesn't work.

so instead of showing me for example 220 for kitchen lcq items for property 1 I'm getting some massive number such as 230,000.

why could this be?

Regards,

Yomna

Re: Group By multiple fields

jagan — Tue, 02 Feb 2016 01:06:49 GMT

Hi,

Use below expressions

LOAD

[Property Reference],

SUM(IF(([Total Cost] > 0) AND (WildMatch([SOR Code], 'kit*') AND (Upper([Work Programme Bk]) = 'LCQ'), [Total Cost], 0] as KIT_Total_LCQ_Cost

Resident EDW

Group by [Property Reference], [Work Programme Bk] , [SOR Code];

OR just load the required values by using where conditions

LOAD

[Property Reference],

SUM([Total Cost] ) as KIT_Total_LCQ_Cost

Resident EDW

WHERE [Total Cost] > 0 AND WildMatch([SOR Code], 'kit*') AND Upper([Work Programme Bk]) = 'LCQ'

Group by [Property Reference];

Hope this helps you.

Regards,

jagan.

Re: Group By multiple fields

Tue, 02 Feb 2016 01:11:37 GMT

Hi Jagan,

Just interested why would you need to add the upper function? what does that do?

I'll try it out now and let you know

thanks

Regards,

Yomna

Re: Group By multiple fields

Tue, 02 Feb 2016 22:30:37 GMT

so it turns out the script is still giving me the wrong information again

its giving me completely different values... and I thought it was related to fixing up the logic but apparently not.

i'm sure its related to the load... but I don't know how to fix this..

Re: Group By multiple fields

swuehl — Tue, 02 Feb 2016 22:44:25 GMT

It's hard to help with the information provided.

You can create a similar straight table with property reference as dimension and sum([Total Cost]) as expression in the frontend, then filter the other fields by selecting in the list boxes. Do you see the same numbers as shown in the aggregated table in the data model?

Or why do you think the numbers are off?

Re: Group By multiple fields

Tue, 02 Feb 2016 22:48:13 GMT

well the problem is that I need to hard code this to be use this information for another function...

so in other words i'm trying to figure out whether money has been spent on certain lcq items with condition data coming from another table.

Re: Group By multiple fields

swuehl — Tue, 02 Feb 2016 23:08:17 GMT

I don't understand what you are telling me. Maybe we continue in your other thread with some more details added by you?

Re: Group By multiple fields

jagan — Wed, 03 Feb 2016 00:34:56 GMT

Hi,

To handle the upper and lower casing differences,

Upper([Work Programme Bk]) = 'LCQ'

even if you [Work Programme Bk] is lcq this condition works, because we are converting to upper case. This is a good practice when comparing strings. Qlikview/Qliksense is case sensitive LCQ & lcq are different.

Regards,

jagan.

Re: Group By multiple fields

Wed, 03 Feb 2016 00:37:11 GMT

Hi Jagan,

In fact LCQ is actually only LCQ in my data set, I've just shown you the possibilities in the data set and I was lazy to have everything in the right case.

but thanks for helping

Re: Group By multiple fields

settu_periasamy — Wed, 03 Feb 2016 02:46:40 GMT

Hi,

In your script, there is missing the Single Quote and Closed parenthesis...

LOAD

[Property Reference],

SUM(IF(([Total Cost] > 0) AND ([SOR Code] like 'KIT*') AND ([Work Programme Bk] like 'LCQ'), [Total Cost], 0] ) ) as KIT_Total_LCQ_Cost

Resident EDW

Group by [Property Reference] and [Work Programme Bk] and [SOR Code];

Now, it seems working..

EDW:
LOAD * INLINE [
    Property Reference  ,  SOR Code ,  Work Programme Bk  ,  Location  ,  Total Cost
    1, kit01, LCQ, kitchen, 100
    1, kit03, LCQ, kitchen, 120
    1, kit04, PR3, kitchen, 140
    1, pgi01, LCQ, bedroom, 95
    1, pgi03, LCQ, bedroom, 150
    1, plu40, SOW, bath, 400
    2, kit50, LCQ, kitchen, 200
    2, plu05, LCQ, bath, 400
    2, ele30, LCQ, WC, 350
    2, min95, RSW, HALL, 40
];

New:
LOAD
[Property Reference],
SUM(IF( ([Total Cost] > 0) AND ([SOR Code] like 'KIT*') AND ([Work Programme Bk] like 'LCQ'), [Total Cost], 0))as KIT_Total_LCQ_Cost
Resident EDW
Group by [Property Reference];

Re: Group By multiple fields

Wed, 03 Feb 2016 03:25:46 GMT

Sorry I actually hadn't copied and pasted this, I wrote it from scratch in this text box so yes I had it the way you are suggesting.

but the problem is still that there are duplicated values being summed up somehow. i'm still trying to figure out why

Re: Group By multiple fields

Wed, 03 Feb 2016 05:44:42 GMT

I mentioned in my other thread that I actually found that the join done at the end of the EDW table was what was causing the problem.

thanks for all your assistance guys!

Regards,

Yomna

Re: Group By multiple fields

jonathandienst — Wed, 03 Feb 2016 06:04:01 GMT

This sounds like it may be a data modelling problem causing the double counting. But its impossible to diagnose the problem exactly without more information - what would be most useful would be your qvw (or a representative sample) and some sample source data in a qvd/slx/csv file.

Re: Group By multiple fields

Thu, 04 Feb 2016 02:58:44 GMT

You're right Jonathan, the left join was causing the problem. so i need to use apply maps instead for the fields i joined to that table. but the fields arent really necessary now. when i actually need them i'll actually fix that up.

Yomna.