I'm having trouble assigning alias names to Oracle tables in QlikView script. Here's a script I've written and below it is how I theoretically want it to be written:
CATEGORIZATION_HIERARCHY:
LOAD
ID,
"PARENT_ID",
"INSTANCE_ID",
"LEVEL_NUM",
"ID_NAME",
"PARENT_ID_NAME";
SQL SELECT *
FROM WHS."HIERARCHY"
WHERE INSTANCE_ID = 7000;
What I'm trying to get to is something like this:
CATEGORIZATION_HIERARCHY:
LOAD
H.ID,
"H.PARENT_ID",
"H.INSTANCE_ID",
"H.LEVEL_NUM",
"H.ID_NAME" as SUBCATEGORY,
"H2.ID_NAME" as CATEGORY,
"H.PARENT_ID_NAME";
SQL SELECT
H.ID,
"H.PARENT_ID",
"H.INSTANCE_ID",
"H.LEVEL_NUM",
"H.ID_NAME" as SUBCATEGORY,
"H2.ID_NAME" as CATEGORY,
"H.PARENT_ID_NAME";
FROM WHS."HIERARCHY" H, WHS."HIERARCHY" H2
WHERE INSTANCE_ID = 7000
AND H.PARENT_ID = H2.ID;
When I run revised script, I get the following error:
Thanks, Sunny! This was close, but it didn't work. Turns out that aliasing after SQL statement is done in database where the command is sent, in my case command was sent to Oracle DB and aliasing was done there. As aliasing was complete the SQL SELECT statement generated output table. When Oracle outputted table, it got rid of alias prefix in field names, so when I wrote H.ID or H.PARENT_ID after the LOAD statement, QlikView did not find these fields because in the Oracle DB they were named ID and PARENT_ID.
The solution is either to go with LOAD *; statement following by wither my or your SQL SELECT statement or to rename field names to exclude alias table name from field names.
