Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 -
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.
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
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.
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.
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.
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
Hi,
The samples were disconnected, yes.
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.
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