Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Adding constant in loaded table with left join

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

Tags (2)
4 Replies
jvitantonio
Not applicable

Adding constant in loaded table with left join

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

Not applicable

Adding constant in loaded table with left join

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?

jvitantonio
Not applicable

Adding constant in loaded table with left join

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

computeJoin:

LEFT JOIN (MAIN)

load * resident COMPUTED;

Not applicable

Adding constant in loaded table with left join

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