topic Re: Link Table issue in QlikView

Link Table issue

samp — Fri, 06 Apr 2018 06:39:50 GMT

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

];

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.

Re: Link Table issue

felipedl — Fri, 06 Apr 2018 12:03:06 GMT

Hi Sam,

As to the autonumberhas128 function:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctions/autonumberhash128.htm

"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.

Re: Link Table issue

samp — Fri, 06 Apr 2018 14:46:39 GMT

Thanks Felipe, but I am mostly looking for the data model, I added keys here uing autonumberhash128 for samples.