topic Column (String) comparison in Where clause of Load Resident ? in QlikView

Column (String) comparison in Where clause of Load Resident ?

Wed, 02 Dec 2009 14:53:45 GMT

Hello,
I recently started using Qlikview and need some help on this issue:

I am trying to join two tables - one is a previously loaded table Combo_Table1 with another table TableC based on a column value which is a string

Combo_Table1:

LOAD "R_ID",
"START_DATE",
"UNIT_OF_MEASURE",
"UPDATE_FLAG",
"VENDOR_NAME";
select "R_ID",
"START_DATE",
"UNIT_OF_MEASURE",
"UPDATE_FLAG",
"VENDOR_NAME" from TableA;

LEFT JOIN SQL SELECT
ACTIVE,
PERIOD,
"R_ID",
"R_NAME"
FROM TableB where ACTIVE = 'Y';

// Now I have another table TableC. Need to combine string column R_NAME of Combo_Table1 with TableC's string column CODE
// Also need to convert R_NAME string to uppercase
// TableC has columns ID,CODE,MEANING and COMMENTS

// The below statement doesn't work. Getting syntax errors plus not sure how to combine a previously loaded table with another table using a WHERE clause.

LOAD ID,
CODE,
MEANING,
COMMENTS
RESIDENT Combo_Table1
WHERE UPPER(R_NAME) = CODE

Can I compare two columns containing Strings in LOAD where clause?
Can someone please advice me on how to accomplish this?

Thanks

Column (String) comparison in Where clause of Load Resident ?

Wed, 02 Dec 2009 18:11:01 GMT

Hi,

Combo_Table1:

TableC:

Load

ID,

Upper(CODE) as R_NAME,

MEANING ,

COMMENTS from tableC ;

it will automatically link both the tables ..

please try out........

Hope this is the same ,you are looking for ............

Column (String) comparison in Where clause of Load Resident ?

Thu, 03 Dec 2009 07:30:44 GMT

Hi Rahul,

Thanks for the reply. This was very helpful. I was able to connect TableC with Combo_Table1 with what you suggested.

The only minor change I did was I used RESIDENT. This is because the TableC is a huge table and I needed to restrict it with certain type.
Somehow when I tried to use WHERE clause in the LOAD statement, it gave me syntax error. So I had to load specific rows of TableC into TempTable first.

*********************************************
TempTable:
LOAD ID, CODE,MEANING, COMMENTS;
SELECT ID, CODE, MEANING, COMMENTS FROM TableC where LOOKUP_TYPE = 'T' ;

Combo_Table1:
LOAD "R_ID",
"START_DATE",
"UNIT_OF_MEASURE",
"UPDATE_FLAG",
"VENDOR_NAME";
select "R_ID",
"START_DATE",
"UNIT_OF_MEASURE",
"UPDATE_FLAG",
"VENDOR_NAME" from TableA;

LEFT JOIN SQL SELECT
ACTIVE,
PERIOD,
"R_ID",
Upper( "R_NAME" ) as R_NAME
FROM TableB where ACTIVE = 'Y';

TableC2:

Load ID,
Upper(CODE) as R_NAME,
MEANING,
COMMENTS RESIDENT TempTable;

*******************************************
I have one question :
Is it possible to combine columns of both the Combo_Table1 and TableC2 and load into one final table ? Both the Combo_Table1 and FinalTable are logically connected thru R_NAME column but I need to have only one table "FinalTable" from which I can select all the columns.
I tried using Load in different ways and not getting the results.

Column (String) comparison in Where clause of Load Resident ?

Fri, 04 Dec 2009 15:53:40 GMT

Hi,

have you tried by joining the table ..?

Column (String) comparison in Where clause of Load Resident ?

Tue, 08 Dec 2009 01:12:16 GMT

Hi Rahul,

I tried LEFT JOIN and it works. Infact, I was able to join 4 tables and the end result is only one clean table with all the desired rows.

Thank you so much for your help.