topic Left Join in QlikView

Left Join

vauban — Thu, 28 Feb 2019 10:17:04 GMT

Hello

I have these two tables

Entete:

IDEntete,

Parameter;

Ligne:

IDLigne,

IDEntete,

Number;

My issue is that if Parameter = 'A' then Number must be multiplied by -1.

I guess I should do a left join with a calculated field, is this the right solution and what would the be syntax ?

i there a way to recalculate Number without a left join ?

regards

Vauban

Re: Left Join

PrashantSangle — Thu, 28 Feb 2019 10:36:44 GMT

If you are looking for answer to recalculate Number without using left join then yes we can do it.
but it involve multiple step which you can avoid by doing left join.

1: With out using Left Join
>>>
Entete:
Load
IDEntete,
Parameter
from table1
where Parameter='A'

Ligne:

Load IDLigne,
IDEntete,
Number*-1 as Number
where exists(InEntete,IDEntete);

concatenate

Load IDLigne,
IDEntete,
Number as Number
where not exists(InEntete,IDEntete);

Regards,
Prashant Sangle

Re: Left Join

vauban — Thu, 28 Feb 2019 12:32:58 GMT

Fine Thank you.

What would be the syntax if I wanted to use left join and get the number * -1 ?

what do you recommend in term of performance ?

Regards

Vauban

Re: Left Join

veidlburkhard — Fri, 01 Mar 2019 10:40:43 GMT

Hi Vauban,

my proposal is to use ApplyMap.

1. Mapping table:

MapParameter:
Mapping
Load
IDEntete,
Parameter
Resident Entete;
Drop Table Entete;

2. Enter 'Parameter' in table 'Ligne':

Ligne:

Load
IDLigne,
IDEntete,
ApplyMap('MapParameter', IDEntete) as Parameter,
Number;

Now you have Parameter and Number in one table and can do your calculation.

Happy qliking

Burkhard