Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
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.
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
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.)
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?
Hold off any answers for now. I think I'm making Progress with Rob's joined version!
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!