topic Adding constant in loaded table with left join in QlikView

Adding constant in loaded table with left join

Thu, 15 Mar 2012 15:12:33 GMT

Hi all,

I have a MAIN table in my script from which I want to extract only 2 fields ([cod_gruppo], [polizza_short]) that satisfy 2 criterias ([cod_gruppo]='222' and [polizza_short]='609') grouped by the 2 fields and add to this COMPUTED table a field with a constant value ([constant]='def').

Once I have the MAIN table and the COMPUTED table (I guess they have to be separate table in order to make the next step) , I want to make a left join between these to tables on the 2 fields ([cod_gruppo], [polizza_short]) so that I can add the field with the constant to the MAIN table on all the fields that appear in che COMPUTED table.

Not sure I make myself clear, but especially not sure how to do it.

All suggestions are appreciated. Thanks

Adding constant in loaded table with left join

jvitantonio — Thu, 15 Mar 2012 15:21:11 GMT

I'm not sure if I understood but your COMPUTED table will have only 1 record?

[cod_grouppo], [polizza_short], [constante]

222, 609 def

Is this what you want to accomplish? If this is the case do:

MAIN:

LOAD * FROM MAINtable;

COMPUTED:

LOAD

[cod_gruppo], [polizza_short], 'def' as constant

RESIDENT MAIN

where [cod_gruppo] = '222' and [polizza_short]='609';

Here you will have the 2 tables. then you can left join them

Adding constant in loaded table with left join

Thu, 15 Mar 2012 15:58:09 GMT

Hi, thanks for your reply.

Sorry, my mistake. The COMPUTED table will have many records (many [contracts] related to [clients]) that have to be grouped, but only where the fields [cod_gruppo] and [polizza_short] satisfy the conditions.

For all the records resulting from this "filtering" I want to add the computed item.

Having the COMPUTED table, I then want to join it with the MAIN table so that i can "mark" the all the [clients] in the main table where they appear in the COMPUTED table. Forget about the [contract] field in the join.

Would be easy in MSaccess....

What would the code be in order to make a left join between the 2 tables that are now resident in QV?

Adding constant in loaded table with left join

jvitantonio — Thu, 15 Mar 2012 16:07:29 GMT

Ok, then you left join by Clients field. Do this:

computeJoin:

LEFT JOIN (MAIN)

load * resident COMPUTED;

Adding constant in loaded table with left join

Thu, 15 Mar 2012 16:28:13 GMT

Not sure I got it all figured out:

load * from [MAIN];

[COMPUTED]:

load [clients], [cod_gruppo], [polizza_short], 'constant' as constant where [polizza_short]='609' and [cod_gruppo] = '222';

left join [MAIN]

load * resident COMPUTED;

drop table COMPUTED;

The result of this should be that I have only a MAIN table with a [computed] field that identifies all the records where [polizza_short]='609' and [cod_gruppo] = '222'.

Thanks