Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
acbishop2
Creator
Creator

Joining multiple generic loads.

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:

LoanIdStateAmount
12345MI200000
12346MA250000
12347MS170000
12348MN190000

 

TeamMembers:

LoanIdRoleTeamMember
12345Loan OfficerJames
12345AssistantJohn
12345ProcessorJerry
12346Loan OfficerJessica
12346AssistantJohn
12346ProcessorJasmine
12347Loan OfficerJames
12348Loan OfficerJessica

 

Status:

LoanIdStatusDate
12345Created1/1/2019
12345Processing1/3/2019
12345Closed1/7/2019
12346Created1/4/2019
12346Processing1/5/2019
12346Closed1/8/2019
12347Created1/8/2019
12348Created1/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:

LoanIdStateAmountLoan OfficerAssistantProcessorCreatedProcessingClosed
12345MI200000JamesJohnJerry1/1/20191/3/20191/7/2019
12346MA250000JessicaJohnJasmine1/4/20191/5/20191/8/2019
12347MS170000James  1/8/2019  
12348MN190000Jessica  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!

Labels (3)
1 Solution

Accepted Solutions
acbishop2
Creator
Creator
Author

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!

View solution in original post

8 Replies
zzyjordan
Creator II
Creator II

Hi,
Can you provide your load script?

ZZ
acbishop2
Creator
Creator
Author

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
Creator II
Creator II

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;

Untitled.jpg

 

Hope this helps

ZZ

acbishop2
Creator
Creator
Author

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
Creator II
Creator II

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
Creator
Creator
Author

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:

LoanIdStateAmountLoan OfficerAssistantProcessorCreatedProcessingClosed
12345MI200000James     
12345MI200000 John    
12345MI200000  Jerry   
12345MI200000   1/1/2019  
12345MI200000    1/3/2019 
12345MI200000     1/7/2019 

 

Am I doing it wrong?

acbishop2
Creator
Creator
Author

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

acbishop2
Creator
Creator
Author

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!