Conditional inclusion of data in a Pivot Table...

Wed, 11 Jan 2012 19:44:43 GMT

I am new to Qlikview, so any help would be much appreciated.

I have a series of MS Excel sheets loaded into Qlikview.

I have the following fields in play:

Part ID, Price Change, and an expression field which counts the number of times the Part ID appears in the spreadsheets.

A Part ID number can be repeated several times, sometimes with the same prices attached in the database. I need to count how many times the price change number changes, excluding a "0" value, for each unique Part ID.

How would I accomplish this?

Best Regards,

Bill.

Re: Conditional inclusion of data in a Pivot Table...

Miguel_Angel_Baeyens — Wed, 11 Jan 2012 22:42:13 GMT

Hi Bill,

As a general rule on getting counters at script level, take a look, among many others at this and this threads and some others mentioned on them, where some sample and working code is posted to solve similar issues. Take a look at the following dummy script based on your description. Okay, it may seem too complex, but let's take a closer look.

DataOriginal is the source table, no calculations done so far, only data. At a first glance, we can see that there are two unique Part ID values A and B, they are not sorted in any particular order (as they will likely be retrieved from no matter the source). A has 4 different price changes, but one of them is zero and there are two equal price changes (and according to my script, I'm not counting them). B has 5 different price changes, but three of them are zero. So A should have 2 changes and B 3.

DataOriginal:
LOAD * INLINE [
Part ID, Price Change
A, 1
A, 2
B, 3
B, 2
A, 0
A, 1
B, 0
B, 0
B, 1
];

DataChanges:
LOAD "Part ID",
     "Price Change",
     If("Part ID" = Previous("Part ID"), 
          If("Price Change" <> 0, 
               If("Price Change" <> Previous("Price Change"), RangeSum(Peek('HasChanged'), 1), Peek('HasChanged'))
          , 0)
     , 0) AS HasChanged
RESIDENT DataOriginal
ORDER BY "Part ID", "Price Change" ASC;

DROP TABLE DataOriginal;

NoOfChanges:
LOAD "Part ID",
     Max(HasChanged) AS TotalNoOfChanges
RESIDENT DataChanges
GROUP BY "Part ID";

The second table does most of the trick, first, it checks that the Part ID is the same as in the previous record. Note that as the table is order by Part ID, all A values will go together, then the B, and that likewise the price changes are ordered ascending, so I can easily compare to the previous value. If the price change is not zero, then add 1 to the HasChanged counter field. But if the Part ID is not the same as in the previous record, then set the counter to zero because another Part ID starts its count.

Another tricky part is the use of the Peek() function. Fields only exist once the table has been completely loaded. "HasChanged" will not exist until the last row from the table DataOriginal is loaded. Well, kind of. Fields do only exist once the row has been loaded, but you can retrieve a value from that not-still-existing-field using Peek() (read "give me the lastest value of the field HasChanged"). Peek() has some other uses, as well.

But say I only want to know how many changes each Part ID has had, there the second table does a simple aggregation using the Max() function to return, group by Part ID the highest value of each counter.

Hope that makes sense.

Miguel

Updated: Attached application with the script and simple table boxes.

topic Re: Conditional inclusion of data in a Pivot Table... in QlikView

Conditional inclusion of data in a Pivot Table...

Re: Conditional inclusion of data in a Pivot Table...