topic Help needed on history data with deltas/diffs, to calculate complete totals in QlikView

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 15:35:57 GMT

Hi all,

I have a set of Data I imported which contains data in this format:

UID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	A	2010-02-02	Some info 1	Some info 2
002	2	A	2010-02-02	Some info 1	Some info 2
003	1	B	2010-02-05	Some info 123	Some info 245
004	3	A	2010-02-05	Some info ABC	Some info DEF

Essentially, it contains the changes that are happening to a clients table, keeping the history data for the client changes (active, inactive).

Based on this table I need to extract the active clients for the dates where the updates occurred.

My problem is that I only have the changes (diffs or deltas as I usually refer to) and I can't figure out a way to calculate the number of clients which are active for a given date.

What happens is that if I select just one of the dates I get the count for that date, which will only contain those that were updated on that date and no the ones that are active but didn't have a change on that date (which is most of them).

I was wondering if someone could help me out on this.

The output should be something similar to this:

2010-02-02 2010-02-05

Active 80 85

Inactive 10 5

What I'm getting after several attempts will eventually get similar to this:

2010-02-02 2010-02-05

Active 80 5

Inactive 10 -

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 15:52:09 GMT

As I believe the solution should be the same, I'll post also another example that I'm working on and that I can't get the values correct either.

The data looks similar to the previous example:

UID	ORDER_ID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	1	A	2010-02-02	Some info 1	Some info 2
002	2	1	A	2010-02-02	Some info 1	Some info 2
003	1	1	B	2010-02-05	Some info 123	Some info 245
004	3	1	A	2010-02-05	Some info ABC	Some info DEF
005	4	2	A	2010-02-05	Some info ABC	Some info DEF

The data comprises orders information for a client and I need to do the same thing as previously, but for the orders.

The output expected is something similar to this:

2010-02-02 2010-02-05

A B A B

Client 1 10 2 13 1

Client 2 20 0 10 0

Client 3 5 1 6 0

Thanks in advance for any help on this.

I've been using a Pivot Table to try and get the proper results, using some set expressions, but nothing came close to the expected results.

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 16:04:28 GMT

Hi,

Filter the Status in the Expression itself,

For example:

count({$<Status={'Active'}>} Date)

The above expression will only counts the date which are in active status.

Regards,

Abdul Rahman

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 16:16:12 GMT

Hi abdulrahman ,

I already tried that, the problem is that I only have the differences to the days after the status change has happened, and not the whole set of data to compare to.

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 16:40:46 GMT

Hi,

Then Try "Flag Method",

You are arriving the difference/ delta,

If your delta > 0, then alias a field "IsUpdated" , and set it to 1 otherwise 0.

So, based on the "IsUpdated" flag, you try to count the orders.

Hope it helps.

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 16:54:36 GMT

Hi,

I already tried something similar and it doesn't work.

I can flag the data, but it will be the same set as using a set expression for the {"=update_date = max(update_date)"}, it will only give me the changes that happened in that day and not the total orders still active for the client on that specific day.

Thanks for the suggestion though.

Help needed on history data with deltas/diffs, to calculate complete totals

Anonymous — Mon, 22 Feb 2010 17:24:29 GMT

I am a wee bit confused. Let me try and figure out what you want to do...Here's your input data

UID	ORDER_ID	CLIENT_ID	STATUS	UPDATE_DATE	INFO1	INFO2
001	1	1	A	2010-02-02	Some info 1	Some info 2
002	2	1	A	2010-02-02	Some info 1	Some info 2
003	1	1	B	2010-02-05	Some info 123	Some info 245
004	3	1	A	2010-02-05	Some info ABC	Some info DEF
005	4	2	A	2010-02-05	Some info ABC	Some info DEF

Would the output based on "just" this above provided data be as follows? I am having a disconnect between your example data and the output you provided.

2010-02-02 2010-02-05

A B A B

Client 1 2 0 1 1

Client 2 0 0 1 0

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 17:28:14 GMT

Hi,

The samples were disconnected, yes.

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 17:31:37 GMT

The second sample should then be:

2010-02-02 2010-02-05

A B A B

Client 1 2 0 2 1

Client 2 0 0 1 0

Sorry about that.

Help needed on history data with deltas/diffs, to calculate complete totals

Anonymous — Mon, 22 Feb 2010 17:32:04 GMT

Lol, OK! But did I get it right? Based on just the five rows of data you provided, is the following output what you'd expect?

2010-02-02 2010-02-05

A B A B

Client 1 2 0 1 1

Client 2 0 0 1 0

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 17:34:41 GMT

Hi,

There should be two active orders for Client 1 on the 5th, since a new order came in, and another one was still active.

Help needed on history data with deltas/diffs, to calculate complete totals

Anonymous — Mon, 22 Feb 2010 17:41:30 GMT

Really? I count two total records for client 1 on the 5th. One is A (I assume active) and the other B (inactive I guess). Either way, something like this should work

load client_id, update_date, sum(if(status='A',1,0)) as Active, sum(if(status='B',1,0)) as Inactive
resient YourSourceData group by client_id, update_date

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 17:48:29 GMT

Hi,

As I mentioned in the post, the data only contains the differences, so if you look at it you will find that order nr 2 is still active on day 5, but since there were no changes, there wasn't a record created for order nr 2 on day 5, yet it should still be accounted as active. And this is what is taking my time, trying to figure out a solution for this.

I will see if I can do something with what you mention, but from a quick view I think probably not.

Thanks.

Help needed on history data with deltas/diffs, to calculate complete totals

RicardoRamos — Mon, 22 Feb 2010 19:53:08 GMT

In logical terms here is what you need:

1-All records where the date is bigger than the update date minus all the records where the date is smaller than the update date;

2-All records with Active status to true.

Here is something that might work:

count({<STATUS={'A'}, UPDATE_DATE={"<= $(vDate)"}-{>$(vDate)}>}CLIENT_ID)

Help needed on history data with deltas/diffs, to calculate complete totals

Mon, 22 Feb 2010 20:11:34 GMT

Hi ricardo,

This should work for the client scenario.

Did you get the chance to look into the order scenario?

Thanks.