Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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

4 Replies
jvitantonio
Specialist III
Specialist III

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
Author

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
Specialist III
Specialist III

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

computeJoin:

LEFT JOIN (MAIN)

load * resident COMPUTED;

Not applicable
Author

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