Joining multiple generic loads.

[acbishop2]

Hi all,

I have a normalized, multi-dimensional database that I need to flatten in a couple of ways. For the sake of simplicity, I have a Loans table, a TeamMembers table, and a Status table, looking somewhat like this:

Loans:

LoanId	State	Amount
12345	MI	200000
12346	MA	250000
12347	MS	170000
12348	MN	190000

TeamMembers:

LoanId	Role	TeamMember
12345	Loan Officer	James
12345	Assistant	John
12345	Processor	Jerry
12346	Loan Officer	Jessica
12346	Assistant	John
12346	Processor	Jasmine
12347	Loan Officer	James
12348	Loan Officer	Jessica

Status:

LoanId	Status	Date
12345	Created	1/1/2019
12345	Processing	1/3/2019
12345	Closed	1/7/2019
12346	Created	1/4/2019
12346	Processing	1/5/2019
12346	Closed	1/8/2019
12347	Created	1/8/2019
12348	Created	1/8/2019

I need to be able to join the tables together, but for the data to be useful I also need to perform a generic load on the TeamMembers and Status tables. The end product that I'm looking for is this:

LoanId	State	Amount	Loan Officer	Assistant	Processor	Created	Processing	Closed
12345	MI	200000	James	John	Jerry	1/1/2019	1/3/2019	1/7/2019
12346	MA	250000	Jessica	John	Jasmine	1/4/2019	1/5/2019	1/8/2019
12347	MS	170000	James			1/8/2019
12348	MN	190000	Jessica			1/8/2019

The problem that I'm running into is that if I perform the generic loads and join at the same time, I get an error saying that the prefixes are incompatible (Join Generic Load is not a valid combo), but if I use the generic load before joining, I have a ton of tables to deal with and a join doesn't work when I use use the new generic table names. Any ideas on how I overcome this?

Any help is appreciated!

[zzyjordan]

Hi,
Can you provide your load script?

ZZ

[acbishop2]

Well, mine doesn't work, but the simplified version would be this:

[Combined Table]:
Generic Load
    LoanId,
    Role,
    TeamMember
From TeamMembers;
Join
Generic Load
    LoanId,
    Status,
    Date
From Status;
Join
Load
    LoanId,
    State,
    Amount
From Loans;

Again, this gives me the illegal combination of prefixes error. Using Generic Load beforehand just makes it impossible to join the tables afterwards (as far as I can tell), and I haven't been able to get the loop to re-join the generic table (found here) to work either.

[zzyjordan]

Thanks for sharing your script, one thing I am not quite understanding is why are you using join instead of the qlik native association, which is loading team data separately and it will automatically associated with the loanid.

Below is my modified script as well as the screenshot of result

Team:
Generic Load
LoanId,
Role,
TeamMember
From TeamMembers;
Status:
Generic Load
LoanId,
Status,
Date
From Status;
Loans:
Load
LoanId,
State,
Amount
From Loans;

Hope this helps

ZZ

[acbishop2]

Yeah, I guess I should have made my reasoning clear. The main reason is that I need to perform operations on data points from different tables, as well as tie Loans to their "Owners" even when there isn't a "Loan Officer"

So let me modify my script and hopefully give you a better idea of why I need the tables to be joined:

[Combined Table]:
Generic Load
    LoanId,
    Role,
    TeamMember
From TeamMembers;
Join
Generic Load
    LoanId,
    Status,
    Date
From Status;
Join
Load
    LoanId,
    [Loan Owner],
    State,
    Amount
From Loans;

[Final Table]:
Load
    LoanId,
    State,
    Amount,
    If(Not(IsNull([Loan Officer])),[Loan Officer],[Loan Owner]) AS [Loan Officer],
    Assistant,
    Processor,
    Created,
    Processing,
    Closed,
    If(Not(IsNull(Closed)),Amount) AS ClosedLoanAmount
RESIDENT [Combined Table];
DROP TABLE [Combined Table];

 
Does that make sense? I can't perform the operations on the data from different tables if they aren't joined. If there's a way to do that, I'm all ears. (Note: In all reality, I'm joining quite a few more tables than this and the formulas are much more complex and sometimes require data points from 3 or 4 different tables. The only way that I know of to perform such operations is to join the tables.)

[zzyjordan]

Hi,
Thanks for your explanation and sorry to misunderstood your requirement initially.

Below find below my modified script to join the data the way you expected.

Loans:
LOAD
LoanId,
State,
Amount
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is Loans);

Left Join(Loans)

load LoanId,TeamMember as "Loan Officer"
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is TeamMembers)
where Role = 'Loan Officer';

left Join (Loans)
load LoanId,TeamMember as "Assistant"
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is TeamMembers)
where Role = 'Assistant';

left Join (Loans)
load LoanId,TeamMember as "Processor"
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is TeamMembers)
where Role = 'Processor';

Left Join(Loans)

load LoanId,Date as "Created"
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is Status)
where Status = 'Created';

left Join (Loans)
load LoanId,Date as "Processing"
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is Status)
where Status = 'Processing';

left Join (Loans)
load LoanId,Date as "Closed"
FROM [lib://AttachedFiles/0110.xlsx]
(ooxml, embedded labels, table is Status)
where Status = 'Closed';

and then use it from your final script
Hope this helps

ZZ

[acbishop2]

Yes, that helps me see things in a different way! The problem is that in all reality, the Status table has about 30 different statuses, not just 3. If I need to do this for all 30 statuses, it's a pain but I can handle it.

On other threads I've seen, they say that we can combine the generic tables again with this:

CombinedGenericTable:
Load distinct Key From GenericDB;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'GenericLabel.*') THEN
  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

(Credit @rwunderlich)

Yet when I try this, instead of getting my desired output (in original post), I get something more like this:

LoanId	State	Amount	Loan Officer	Assistant	Processor	Created	Processing	Closed
12345	MI	200000	James
12345	MI	200000		John
12345	MI	200000			Jerry
12345	MI	200000				1/1/2019
12345	MI	200000					1/3/2019
12345	MI	200000						1/7/2019

Am I doing it wrong?

[acbishop2]

Hold off any answers for now. I think I'm making Progress with Rob's joined version!

[acbishop2 · Accepted solution]

Ok, I was able to fix this using the for loop that was suggested on the thread mentioned. I was confused as to what to use as the variables. This was the solution:

[GenericTeamMembers]:
Generic Load
    LoanId,
    Role,
    TeamMember
From TeamMembers;


TeamMembersJoin:
Load Distinct OpportunityId From TeamMembers;

FOR i = NoOfTables()-1 to 0 STEP -1;
	LET vTable=TableName($(i));
    IF WildMatch('$(vTable)','GenericTeamMembers.*') THEN
    LEFT JOIN ([TeamMembersJoin]) LOAD * RESIDENT [$(vTable)];
	DROP TABLE [$(vTable)];
    ENDIF
NEXT i

The same was done for the Status table, and then both joined tables were joined together and joined to the Loans table.

Thank you for the help and ideas!