Problem of Finding SUM TOTALPRICE GROUP BY PROMOTION

[Jefflee0915]

Hi Experts,

I'm using Qlikview 11 & I have the following table:

Duplicate Row is highlighted in yellow.

Let say SPLITTED_PROMOID 175 exist in pid 2644217, I need to sum TOTALPRICE that pid=2644217 which is row 1-9 in image above (even though SPLITTED_PROMOID row 1,3,5-9 not 175). But in pid=2644217 notice that row 1,2 AND 3,4 AND 8,9 have duplicate Ids, In this case I need to check SPLITTED_PROMOID. Since row 2 & 4 contains 175 which means row 1 & 3 is duplicate and should not be sum to total. And for row 8 & 9 don't contain 175, this case just random pick 1 row as long as the id only add 1 time. Which the result is row 2+4+5+6+7+8or9 = 870.

This may looks like SUM(TOTALPRICE) Order by SPLITTED_PROMOID. BUT THIS WILL OUTPUT row 2+4 = 212.1. Which is not what I'm looking for.

For another case SPLITTED_PROMOID 169 exist in pid 2644217, 2644203 and 2644220. I'll split into 3 parts of calculation.

pid: 2644203

I need to sum TOTALPRICE that pid: 2644203 which is row 10 in image above. Since there's only 1 and no duplicate record. Which the sum is row 10 = 89.

pid: 2644220

I need to sum TOTALPRICE that pid: 2644220 which is row 11 & 12 in image above (even though SPLITTED_PROMOID row 11 not 169). But in pid: 2644220 notice that row 11,12 have duplicate Ids, In this case I need to check SPLITTED_PROMOID. Since row 12 contains 169 which means row 11 is duplicate and should not be sum to total. Which the sum is row 12 = 143.

pid: 2644217

I need to sum TOTALPRICE that pid=2644217 which is row 1-9 in image above (even though SPLITTED_PROMOID row 1-8 not 169). But in pid=2644217 notice that row 1,2 AND 3,4 AND 8,9 have duplicate Ids, In this case I need to check SPLITTED_PROMOID. Since row 9 contains 169 which means row 8 is duplicate because of same id and should not be sum to total. And for row 1 & 2 AND 3 & 4 SPLITTED_PROMOID don't contain 169, this case just random pick 1 row for each id as long as the id only add 1 time. Which the sum is row 1or2+3or4+5+6+7+9 = 870.

Now sum all 3 parts 89+143+870 = 1102

May I ask how to possible to code the Expression?

Expected output using Chart Table  :

SPLITTED_PROMOID |  TOTALPRICE

==================|====================================================

141                                     |    1013    (127.26+84.84+183.42+165.74+165.74+143+143)       

                                             |                   (ROW: 1or2+3or4+5+6+7+8+11)

==================|====================================================

167                                     |    870      (127.26+84.84+183.42+165.74+165.74 + 143)                 

                                             |                  (ROW: 1+3+5+6+7+8or9)

==================|====================================================

169                                     |    1102   (127.26+84.84+183.42+165.74+165.74+143+89+143)

                                             |                  (ROW: 1or2+3or4+5+6+7+9+10+12)

==================|====================================================

175                                     |    870      (127.26+84.84+183.42+165.74+165.74+143)

                                             |                  (ROW: 2+4+5+6+7+8or9)

==================|====================================================

Many Thanks!

Regards

Jeff

[Or]

Perhaps I am misreading your post, but it looks like the formula you are presenting for the expected output is just Sum(TOTALPRICE) and you are in fact counting the "duplicate" rows twice? e.g. row 2 which you said you wanted to exclude is in fact being counted under 175 (which in itself doesn't seem to actually match the values under 175 in your source data, perhaps a copying error?)

[Jefflee0915]

@Or  I just update the post hope that's more clear

[Or · Accepted solution]

I think I better understand the requirement now, but I'm not at all sure how to achieve this using the data structure from your table. If I was approaching this on my end, I would likely try and solve it by manipulating the data structure, perhaps something along the lines of two tables:

Table1: ID, PID, Amount, PromoID (one row per ID/PID combination without splitting per promo)

Table 2 - PID, Split PromoID (one row per combination for Promo IDs associated with the PID, ignoring the regular ID)

Table1 would be created using:

Load distinct ID, PID, Amount, PromoID

From OriginalTable;

Table 2 would be created using:

Load Distinct PID, [Split PromoID]

From OriginalTable;

If I'm not mistaken, this approach should allow you to sum by Split PromoID without having to worry about duplication, as the duplicate rows won't exist in the first place (they only exist because the original table is split multiple times to allow for multiple Split PromoID values).

Of course, I'm not sure if that's practical for what you're trying to do, but generally speaking with Qlik the best approach is usually not to lump in two levels of granularity into a single table like one would in an SQL statement, instead keeping them separated into two tables with a key.

[Jefflee0915]

@Or  Thanks for the reply! Good point about splitting into 2 table. The first thing I thought using 1 table is because there's huge amount of data in my DB loading the data to QlikView already takes quite a while. Loading 2 almost double it's time. But I guess I need to split it anyway XD. 

[Or]

If you use Resident loads rather than reload from database multiple times, this shouldn't significantly increase your run time, most likely. If you can load it as two separate queries to begin with, it might even save time.