Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

sampusala
New Contributor II

Link Table issue

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.

2 Replies
felipedl
Valued Contributor III

Re: Link Table issue

Hi Sam,

As to the autonumberhas128 function:

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

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

sampusala
New Contributor II

Re: Link Table issue

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

Community Browser