Hi,
Trying to create link table to have proper associations but could't able to achieve, any help for below sample data?
ORR:
Load *, AutoNumberHash128(userid, acctid, orDate, orname) as %Orrkey
Inline [
userid, acctid, orDate, orname
opp1u, opp9a, '1/30/2018', name1
opp2u, opp8a, '1/31/2018', name2
opp3u, opp7a, '2/1/2018', name3
opp4u, opp6a, '2/2/2018', name4
];
User:
Load *, AutoNumberHash128(userid, acctid) as %Userkey
Inline [
userid, acctid, Div
opp1u, opp9a, A1
opp2u, opp8a, A2
opp3u, rrr, A3
CSMP1u, CSMP9a, B1
CSMP2u, CSMP8a, B2
CSMP3u, fff, B3
];
calendar:
Load * Inline [
calendardate, Quarter
'1/30/2018', Q1
'1/31/2018', Q1
'2/1/2018', Q2
'2/2/2018', Q2
'3/1/2018', Q3
'3/2/2018', Q3
];
Login:
Load *, AutoNumberHash128(userid, logDate) as %Loginkey
Inline[
userid, logDate, empname
CSMP1u, '1/30/2018', CSMP1uname
CSMP2u, '1/31/2018', CSMP2uname
CSMP1u, '3/1/2018', CSMP1uname
CSMP2u, '3/2/2018', CSMP2uname
opp1u, '1/30/2018', opp1uname
opp2u, '1/31/2018', opp2uname
];
Link_table:
load %Orrkey,
userid,
acctid,
orDate as calendardate
resident ORR;
drop fields userid, acctid, orDate from ORR;
Concatenate(Link_table)
load %Loginkey,
userid, // don't have acctid field here
logDate as calendardate
resident Login;
drop fields userid, logDate from Login;
left join (Link_table)
load userid,
acctid,
%Userkey
resident User;
drop fields userid, acctid from User;
exit script
issue is datamodel looks good but not having %Userkey values for %Loginkey so that's the reason I could't able to see Div Values for %LoginKey.
Calendar data associations working fine.
appreciate for your help to get all the data in sample report.
