Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table:
LOAD * INLINE [ mid, sid, ordernum A, A1, - A, A1, - A, A1, -
A, A2, -
A, A3, 123 B, B1, - B, B2, - B, B3, 159
B, B3, -
C, C1, -
C, C2, -
C, C2, -
D, D1, -
. . .
. . . ];
For some context, I am working with data from people who traverse through a website. `mid` is their main id, `sid` is their session. A user, lets say `B` can have multiple distinct sessions `B1`,`B2`, etc. When the session is repeated, it means that they're simply doing an action within a session. If an order took place in a session, It returns an order number.
As you can see, most sessions don't contain an order number. Most importantly, some `mid`s, ie people have never purchased (like C for example). I want to essentially:
Return all `mids` of people who purchased (where `mid` has a non-null, or some numeric number for the `order num`). From that point, I want to count the amount of distinct `sids` for every `mid` and then average them. So example below
Return all `mids` with a non-null or numeric value:
So we would get `A` and `B`. We count the distinct `sids` (A1, A2, A3, B1, B2, B3) and then we divide 6 by the total amount of returned `mids` 6/2
Hope this is clear!
=count(distinct {<mid = P({<ordernum -= {'-'}>} mid)>} sid)
/
count(distinct {<ordernum -= {'-'} >} mid)
you may need to handle nulls differently if these are true nulls or dashes.
=count(distinct {<mid = P({<ordernum -= {'-'}>} mid)>} sid)
/
count(distinct {<ordernum -= {'-'} >} mid)
you may need to handle nulls differently if these are true nulls or dashes.
Can you walk me through how you got to this solution? Where does the `P` come from? Also how would I change this if the dashes are nulls
nulls i would do *={"*"}.
numerator: count(distinct {<mid = P({<ordernum -= {'-'}>} mid)>} sid)
counts sid, and limits mid, to possible mids that have an order num <> "-". or nulls could be ordernum *= {"*"}
denominator:
count mids that have an order num <> "-". or nulls could be ordernum *= {"*"}