topic Re: cal field in QlikView

cal field

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a model with 2 tables, TableA & TableB(both are linked). I want to write a calculated field in tableA like below-

if(field1 = 'aa' and field2 = 'yy', 'n/a', field3) field3

But the field2 is in tableB.

Is there a way to write the condition without bringing the field2 into tableA?

When i try to do resident and join to bring the field2 into tableA, i see change in no. of records and worried that if effect the existing UI calculations.

When i try applymap, i am missing values in field2.

Please suggest the best approach.

Thanks!!

Re: cal field

sunny_talwar — Thu, 15 Sep 2016 13:46:27 GMT

Hi Suraj -

We might be able to help better if you are able to share a sample.

Re: cal field

surajap123 — Thu, 15 Sep 2016 14:01:29 GMT

Hi Sunny, I have attached the test app.

Re: cal field

Thu, 15 Sep 2016 14:32:22 GMT

You can't join Field2 into TableA like this:

Left Join(TableA)

LOAD Field2

Resident TableB;

QlikView wouldn't know how to connect them

This should do the Job:

Left Join(TableA)

LOAD

lid,

Field2 as TableA.Field2

Resident TableB;

Re: cal field

Anonymous — Thu, 15 Sep 2016 14:33:51 GMT

Hi Suraj,

TableA:

LOAD *

INLINE [

lid, Field1, Field3

1, TKT, ET

2, PKT, ME

3, UT, ET

];

TableB:

LOAD * INLINE [

amt, lid, Field2

34, 1, 01/01/2016

363, 1, 01/02/2016

636, 2, 01/05/2016

674, 1, 01/06/2016

356, 3, 01/06/2016

];

Left Join(TableB)

LOAD *

Resident TableA;

Left Join (TableB)

LOAD

lid,

Field2,

Field1,

if(Field1 = 'TKT' and Field2 >= '01/05/2016','ME', Field3) as Field3_New

RESIDENT TableB;

Regards!!!

Re: cal field

miguelbraga — Thu, 15 Sep 2016 14:39:06 GMT

Hey there,

Please make a table where you explain your desired result so that we provide you a solution

I'll try to figure something out of problem...

Regards,