topic Re: Associating Tables in App Development

Associating Tables

nchamilton — Mon, 08 Jul 2024 15:41:59 GMT

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

Re: Associating Tables

BrunPierre — Mon, 08 Jul 2024 17:08:21 GMT

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;

Re: Associating Tables

nchamilton — Mon, 08 Jul 2024 17:11:00 GMT

Perfect, thank you.

Re: Associating Tables

rwunderlich — Mon, 08 Jul 2024 18:18:09 GMT

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

Re: Associating Tables

nchamilton — Mon, 08 Jul 2024 18:20:08 GMT

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