Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Sam,
As to the autonumberhas128 function:
"You can only connect autonumberhash128 keys that have been generated in the same data load, as the integer is generated according to the order the table is read. If you need to use keys that are persistent between data loads, independent of source data sorting, you should use the hash128, hash160 or hash256 functions."
That being said, the autonumber will generate different numbers, since it's not being loaded in a single table, hence the linking problems your experimenting.
Felipe.
Thanks Felipe, but I am mostly looking for the data model, I added keys here uing autonumberhash128 for samples.