Group By multiple fields

[#1]

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

[jagan]

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.

[#3]

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

[settu_periasamy]

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

[#5]

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

[#6 · Accepted solution]

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

[jonathandienst]

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.

[#8]

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.