How do I link tables with a filter

[heij1421]

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?

[GaryGiles · Accepted solution]

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;

[heij1421]

Great, thanks