topic Re: Calculated row in script after joining three tables in QlikView

Calculated row in script after joining three tables

Wed, 11 Sep 2013 13:36:32 GMT

Hello guys, I feel pretty stupid asking this question, I believe I´ve dealt with bigger problems in Qlikview so far, but I can´t seem to be able to solve this one.

I have a field in a particular table, where I would like to add another row based on expression from values in table itself and from another table, which is joined by 3 ids away.
I already have the table (table1, in which id_1 is also present) loaded through binary load and I basically take it, join ids from two different tables to it, then load it into new table (table1_updated), create new field based on ids I´ve joined (original_field_updated) and drop original table (table1) as well as joined ids in newly created one (table1_updated).
The problem is, as soon as I drop these two ids, I loose the connection to the id_3 and 'new_value' doesn´t funcion as intended, taking only id_1 into account.
I can´t leave those ids nor the table1 in, since then it causes syntetic joins with tables I joined ids from, obviously. Here is the script:

LEFT JOIN (table1)
LOAD id_1,
id_2
Resident table2;

LEFT JOIN (table1)
LOAD id_2,
id_3
Resident table3;

table1_updated:
LOAD *,
if(id_1 = x AND id_3 = y, 'new_value', original_field) as original_field_updated
RESIDENT table1;

DROP table table1;
drop Fields id_2, id_3 from table1_updated;

Does any of you know how to solve the issue? Thanks in advance for your time

Re: Calculated row in script after joining three tables

schlettig — Wed, 11 Sep 2013 13:44:04 GMT

Hello Jan,

sounds strange to me, beacause normally the if is executing during that particular table load and so the values are

already calculated, when you drop the fields and the table.

Are the id_1, id_2 and id_3 the same and used only for joining?

Could you check, if it's maybe a concatenating problem?

table1_updated:

noconcatenate

LOAD *,
if(id_1 = x AND id_3 = y, 'new_value', original_field) as original_field_updated
RESIDENT table1;

DROP table table1;
drop Fields id_2, id_3 from table1_updated;

Re: Calculated row in script after joining three tables

Wed, 11 Sep 2013 13:51:26 GMT

Hello Christian,

thank you for being this quick.

Not sure if I understand the question, the ids are used as real ids in other tables, that´s why I need to drop them from table1, in order to prevent unwanted joins.

Unfortunately, that solution is not helping, the new row value is still matched only to the first condition, not the second one (id_3 = y) 😕

Re: Calculated row in script after joining three tables

schlettig — Wed, 11 Sep 2013 13:57:58 GMT

Hi Jan,

you could rename them instead of dropping to see if that changes anything?

Is it possible to show some lines of the 3 tables before joining?

Regards,

Christian

Re: Calculated row in script after joining three tables

michael_maeuser — Wed, 11 Sep 2013 14:03:45 GMT

i assume id2 doesn´t exist in table one before your first left join? if thats correct then your second left join doesn´t work at all even though your first left join is before that one (id 2 still doesn´t exist at this moment).

load your table 1 resident after joing id2 and then join id3

Re: Calculated row in script after joining three tables

Fri, 13 Sep 2013 10:55:00 GMT

Hello Christian,

by using RENAME, even the original ones I need to keep in gets renamed.

Sorry, I can´t present any real data, unfortunately.

In the meantime, I worked around the problem in tables and charts for whose I needed this row in the first place, by putting the line in dimension itself. It´s not as ellegant, but working for the time being.

Re: Calculated row in script after joining three tables

Fri, 13 Sep 2013 10:57:58 GMT

Hi Michael,

no, it doesn´t exist, but the way you suggest doesn´t work since I can´t put reference to a field which is not joined yet into the resident load for table1. Anyway, I´ve worked around the problem the way I described couple of posts above, thanks for your help.

Re: Calculated row in script after joining three tables

schlettig — Fri, 13 Sep 2013 12:56:15 GMT

good to hear you found a workaround.

With the rename i meant no the RENAME directive, but to load the fields with a different name.

load

id_2 as id_2_table2

Regards,

Christian

Re: Calculated row in script after joining three tables

Fri, 13 Sep 2013 13:55:13 GMT

Oh, yes, I´ve tried that in the beginning as well. The problem is, if I rename them in the resident load of table1_updated, QV won´t let me call them for the conditional row I am trying to create (field does not exists).

Although I can call them by their original name, the connection will stop functioning and it is the same as if I would´ve dropped the fields...

Anyway, thanks for all your time, I´ll post solution here in the future, if I find one that is

Have a nice weeekend!