Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Group By multiple fields

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

1 Solution

Accepted Solutions
Not applicable

Re: Group By multiple fields

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

16 Replies
Not applicable

Re: Group By multiple fields

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.

swuehl
Not applicable

Re: Group By multiple fields

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];

Not applicable

Re: Group By multiple fields

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

jagan
Not applicable

Re: Group By multiple fields

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.

Not applicable

Re: Group By multiple fields

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


Not applicable

Re: Group By multiple fields

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..

swuehl
Not applicable

Re: Group By multiple fields

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?

Not applicable

Re: Group By multiple fields

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.

swuehl
Not applicable

Re: Group By multiple fields

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