Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to calculate certain amount of users based on their registration date but i'm not able to get the result. Can anyone help me on the solution ?
I have two Tables:
PLAYER:
[ PLAYER_ID, REG_DATE, UPDATE_DATE,
100, 20180103, 20180302,
101, 20180202, 20180204,
105, 20180204, 20180301];
GAMES:
[PLAYER_ID, GAME, TRAN_DATE,
100, A, 20180103,
100, A, 20180201,
100, A, 20180202,
101, A, 20180202,
101, A, 20180203,
101, A, 20180205,
105, A, 20180204,
105, A, 20180301,
105, A, 20180302];
DateBridge:
Load Distinct PLAYER_ID, UPDATE_DATE as GLB_TIME, UPDATE_DATE
Resident [PLAYER];
Outer Join
Load Distinct PL_PLAYER_ID, TRAN_DATE as GLB_TIME, TRAN_DATE
Resident [GAMES];
I'm trying to get the # players registered per day:
Date # Players Registered
------------------------------------------------------
20180103 1
20180104 0
. . .
20180202 1
. . .
20180204 1
. . .
20180301 0
I have the following measure but it is not working:
#Players Registered = SUM( IF( REG_DATE=GLB_TIME , 1 , 0) )
Thanks in advance,
Francisco
I am not sure I am able to follow... what is the issue that you are running into?
Load the script as below
PLAYER:
Load * Inline [
PLAYER_ID, REG_DATE, UPDATE_DATE,
100, 20180103, 20180302,
101, 20180202, 20180204,
105, 20180204, 20180301
];
GAMES:
LOAD * Inline [
PLAYER_ID, GAME, TRAN_DATE,
100, A, 20180103,
100, A, 20180201,
100, A, 20180202,
101, A, 20180202,
101, A, 20180203,
101, A, 20180205,
105, A, 20180204,
105, A, 20180301,
105, A, 20180302
];
DateBridge:
NoConcatenate
Load Distinct PLAYER_ID, UPDATE_DATE as GLB_TIME_PLAYERS, UPDATE_DATE,REG_DATE
Resident [PLAYER];
Right Join
Load Distinct PLAYER_ID, TRAN_DATE as GLB_TIME_GAMES, TRAN_DATE
Resident [GAMES];
DROP Tables PLAYER,GAMES;
---------------------------------------------------------------------------------
Take a straight table
Dimension : TRAN_DATE (Rename it to date)
Expression: SUM( IF( REG_DATE=GLB_TIME_GAMES , 1 , 0) )
-------------------------------------------------------------------
Deselect the 'Supress Zero' from presentation tab
Hi Pritam,
Thanks for the support.
These are huge tables that unfortunately have much more information and i can't drop it. I understood the rational presented.
Do you think that i have a solution keeping up the tables GAMES and PLAYER ?
Thanks in advance
Francisco
Hi Sunny,
Basically, i have two dates in the Player Table and the records in the Player table are "inline" with the date UPDATE_DATE with the "other Tables".
These two tables PLAYER and GAMES have much more records and are linked in terms of the DATES with other Tables.
I need is to know for the REG_DATE (which is not "linked" in terms of Date) with the other Tables how can i be able to account for each day of the GLB_TIME how many Players have a REG_DATE within each day ?
Thanks,
Francisco