-
Re: Sum values based on a Date
Sunny Talwar Mar 5, 2018 8:04 AM (in response to Francisco Pedrosa)I am not sure I am able to follow... what is the issue that you are running into?
-
Re: Sum values based on a Date
Francisco Pedrosa Mar 5, 2018 9:10 AM (in response to Sunny Talwar )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
-
-
Re: Sum values based on a Date
Pritam Basak Mar 5, 2018 8:11 AM (in response to Francisco Pedrosa)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
-
Re: Sum values based on a Date
Francisco Pedrosa Mar 5, 2018 9:06 AM (in response to Pritam Basak)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
-