Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following tables 3 tables with users, General Ledger accounts and a security table
Usertable:
LOAD * INLINE "
Users,
User1,
User2,
User3,
User4
"(delimiter is ',');
GL:
LOAD * INLINE "
GL_Account,
100000,
200000,
300000,
400000,
500000,
600000,
700000,
800000,
900000,
999999
"(delimiter is ',');
Securitylist:
LOAD * INLINE "
UserID, GL_From, GL_To
User1, 100000, 999999
User2, 400000, 800000
User3, 100000, 300000
User3, 700000, 999999
"(delimiter is ',');
Out of these tables I want to create a new table where I can see which users is allowed to see which GL Accounts.
The outcome should be a
User | GL_Account |
User1 | 100000 |
User1 | 200000 |
User1 | 300000 |
User1 | 400000 |
User1 | 500000 |
User1 | 600000 |
User1 | 700000 |
User1 | 800000 |
User1 | 900000 |
User1 | 999999 |
User2 | 400000 |
User2 | 500000 |
User2 | 600000 |
User2 | 700000 |
User2 | 800000 |
User3 | 100000 |
User3 | 200000 |
User3 | 300000 |
User3 | 700000 |
User3 | 800000 |
User3 | 900000 |
User3 | 999999 |
How do I write a script with this outcome?
Try this after your Inline loads:
NoConcatenate
OutcomeTemp:
Load *
Resident GL;
Join (OutcomeTemp)
Load *
Resident Securitylist;
Outcome:
Load UserID,
GL_Account
Resident OutcomeTemp
where GL_Account >= GL_From and GL_Account <= GL_To;
drop table OutcomeTemp;
Try this after your Inline loads:
NoConcatenate
OutcomeTemp:
Load *
Resident GL;
Join (OutcomeTemp)
Load *
Resident Securitylist;
Outcome:
Load UserID,
GL_Account
Resident OutcomeTemp
where GL_Account >= GL_From and GL_Account <= GL_To;
drop table OutcomeTemp;
Great, thanks