Associating Tables

[nchamilton]

Good morning,

I have 2 tables. A Cards table that consists of a card_id,card_title. 

card_id,card_title

1,Project 1

2,Project 2

3,Project 3

And a table that consists of User Assignments

card_id,user

1,User 1

2,User 2

I want to associate the two tables (without using a join) and create a flag to flag unassigned users. In the Above example, Project 3 would show Unassigned as the user.

I'm looking for a standard best practice. I want to keep both tables in the model. I would prefer not to do a left join because I don't want to have to reload my joined tables as a new table. I attempted to use the apply map but the card_id is missing from the User assignments table so I can't see how to best accomplish that.

My preference is to have a flag in the load script that I can easily use in the chards.(i.e. user_assigned_flag =1 if assigned, 0 if not).

Thanks

[BrunPierre · Accepted solution]

Still using Mapping Load, but do it in this manner.

Cards0:
LOAD * Inline [
card_id, card_title
1, Project 1
2, Project 2
3, Project 3];

UserAssignments:
LOAD *Inline [
card_id, user
1, User 1
2, User 2];

MapAssigments:
Mapping LOAD Distinct card_id,
1 as AssignedFlag
Resident UserAssignments;

Cards:
LOAD card_id,
card_title,
ApplyMap('MapAssigments', card_id, 0) as user_assigned_flag
Resident Cards0;

DROP Table Cards0; 

[nchamilton]

Perfect, thank you.

[rwunderlich]

If you can load UserAssignments first it may be simpler as 

UserAssignments:

LOAD *Inline [

card_id, user

1, User 1

2, User 2];

Cards0:

LOAD *,

-Exists(card_id) as user_assigned_flag

Inline [

card_id, card_title

1, Project 1

2, Project 2

3, Project 3];

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[nchamilton]

Thanks for the reply, I'll check this out as well.