Concatenating an Inline Table?

[#1]

I have a field of PRIVRANK I want to assign numeric values to and concatenate as shown below. I admit I'm not that savvy and when I use the TABLE4BAD code I get this error:

Table not found

TABLE4B:

LOAD PERMISSION,

    CONCAT(PRIVRANK,'',PERMISSION) AS CONCATENATEDFIELDB

RESIDENT Table5 GROUP BY PERMISSION

Is there any way I can concatenate a field created in an inline table OR create a field in Table1 renaming all the "PERMISSION" values?

Table1:
LOAD "SYSTEMKEY",
    "D_THETABLEMARKER",
    "KEY",
    "PRIVILEGE",
    PERMISSION,
    num((AutoNumber(PERMISSION))+3) as DUPEFLAG
//    (AutoNumber(PERMISSION))+1&'X' as DUPEFLAG
    ;
SQL SELECT *
FROM DATABASE;

Table2:
LOAD PRIVILEGEID as PRIVILEGE,
    PRIVILEGENAME,
    SUBSYSTEMNAME,
    PRIVILEGECATEGORY,
    SUBSYSTEMNAME&'-'&PRIVILEGECATEGORY&'-'&PRIVILEGENAME AS CROSSPRIV;
SQL SELECT PRIVILEGEID,
    PRIVILEGENAME,
    SUBSYSTEMNAME,
    PRIVILEGECATEGORY
FROM DATABASE;

Table3:
LOAD PERMISSION,
    PERMISSIONNAME;
SQL SELECT PERMISSION,
    PERMISSIONNAME
FROM DATABASE;

TABLE5:
LOAD * INLINE [
    PERMISSION, PRIVRANK
    4, 1
    6, 2
    7, 3
    A, 3
    JJKLO, 6
    HH, 7
    Eng, 4
    NO, 1
    No Access, 1
    O, 2
    R, 1
    Read, 2
    RKU, 5
    RX, 4
    User, 3
    W, 2
    X, 1
    YES, 2
    FALSE, 1
    TRUE, 2
];

TABLE4BAD:
LOAD PERMISSION,
    CONCAT(PRIVRANK,'',PERMISSION) AS CONCATENATEDFIELD
RESIDENT Table5 GROUP BY PERMISSION;

TABLE4GOOD:

LOAD SYSTEMKEY,

    CONCAT(PERMISSION,'',SYSTEMKEY) AS CONCATENATEDFIELD

RESIDENT Table1 GROUP BY SYSTEMKEY;

[Anonymous]

David,

Table names are case-sensitive.  You have TABLE5, and later you load from Table5 which doesn't exist, hence "not found".

Regards,

Michael

[#3]

Beginner mistake, thanks for pointing that out. Now I have another problem related to the concatenation of PRIVRANK. The code below doesn't work but it's what I need it to do; using PRIVRANK from TABLE5 instead of PERMISSION from TABLE1, while still retaining SYSTEMKEY from Table1.

TABLE4:

LOAD SYSTEMKEY,

    CONCAT(PRIVRANK,'',SYSTEMKEY) AS CONCATENATEDFIELD

RESIDENT Table1 GROUP BY SYSTEMKEY;

Of course i get a field not found error since PRIVRANK isn't in Table1, so how would I pull this off?

[Anonymous · Accepted solution]

Correct, SYSTEMKEY is in Table1, but PRIVRANK in in TABLE5.  But you can join them in a temp table, or better use mapping.  If TABLE5 is not needed anywhere else, use it as a map., simply add preffix MAPPING:

TABLE5:

MAPPING LOAD * INLINE [

    PERMISSION, PRIVRANK

    4, 1

   ...

];

Now, replace PRIVRANK with applymap:

TABLE4:

LOAD SYSTEMKEY,

    CONCAT(applymap('TABLE5', PERMISSION),'',SYSTEMKEY) AS CONCATENATEDFIELD

RESIDENT Table1 GROUP BY SYSTEMKEY;

Regards,

Michael

[#5]

Worked like a charm. Michael, you and the rest of this community have assisted in automating a report pulled from constantly changing table for the purpose of analyzing a 37,000+ row nightmare.