Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Using a field (calculated) from one table in another table

Hello:
I am trying to use a calculated field from one table in another table.

I tried to use ApplyMap() by Mapping JobCode. It didnt work ... I might've not done it the right way. What is the correct way to do this?


Basically below is my sample table structure and the logic trying to use.

Please help. THANK YOU.


Table1 -

Actives:

LOAD

EmployeeID,

JobCode,

ManagerLvl,

IF([JobCode] = '0','Yes',

IF([JobCode]='2','Yes',

IF([JobCode]='9' AND [ManagerLvl] = '8', 'Yes',

IF([JobCode]='9' AND [ManagerLvl] = '9', 'Yes',

IF([JobCode]='9' AND [ManagerLvl] = '10', 'Yes','No'))))) as "Mgr_Agr_Required"

FROM

Actives.xls (biff, embedded labels, table is Detail$);

Table2 -

Agreements:

LOAD

EmployeeID,

Name,

Promotion_Dt,

Mgr_Agr_Dt,

IF(Mgr_Agr_Required='Yes' AND [Mgr_Agr_Dt] = IsNull(),'Non-Compliant','Compliant')) as "Compliance Status"

FROM

Agreements.xls (biff, embedded labels, table is Agreements$);

Thank You.

Regards,
Archana

1 Solution

Accepted Solutions
hector_munoz_in
Valued Contributor

Re: Using a field (calculated) from one table in another table

Hi Archana,

Try to something like this:

Actives:

LOAD

EmployeeID,

JobCode,

ManagerLvl,

IF([JobCode] = '0','Yes',

IF([JobCode]='2','Yes',

IF([JobCode]='9' AND [ManagerLvl] = '8', 'Yes',

IF([JobCode]='9' AND [ManagerLvl] = '9', 'Yes',

IF([JobCode]='9' AND [ManagerLvl] = '10', 'Yes','No'))))) as "Mgr_Agr_Required"

FROM

Actives.xls (biff, embedded labels, table is Detail$);

MAP_MGR_AGR_REQUIRED:

MAPPING LOAD EmployeeID     AS MAP_MGR_AGR_REQUIRED_EmployeeID,

                          Mgr_Agr_Required     AS MAP_MGR_AGR_REQUIRED_Mgr_Agr_Required

RESIDENT     Actives;

Agreements:

LOAD

EmployeeID,

Name,

Promotion_Dt,

Mgr_Agr_Dt,

IF(ApplyMap('MAP_MGR_AGR_REQUIRED', EmployeeID, 'No')='Yes' AND [Mgr_Agr_Dt] = IsNull(),'Non-Compliant','Compliant')) as "Compliance Status"

FROM

Agreements.xls (biff, embedded labels, table is Agreements$);

Regards,
H

3 Replies
hector_munoz_in
Valued Contributor

Re: Using a field (calculated) from one table in another table

Hi Archana,

Try to something like this:

Actives:

LOAD

EmployeeID,

JobCode,

ManagerLvl,

IF([JobCode] = '0','Yes',

IF([JobCode]='2','Yes',

IF([JobCode]='9' AND [ManagerLvl] = '8', 'Yes',

IF([JobCode]='9' AND [ManagerLvl] = '9', 'Yes',

IF([JobCode]='9' AND [ManagerLvl] = '10', 'Yes','No'))))) as "Mgr_Agr_Required"

FROM

Actives.xls (biff, embedded labels, table is Detail$);

MAP_MGR_AGR_REQUIRED:

MAPPING LOAD EmployeeID     AS MAP_MGR_AGR_REQUIRED_EmployeeID,

                          Mgr_Agr_Required     AS MAP_MGR_AGR_REQUIRED_Mgr_Agr_Required

RESIDENT     Actives;

Agreements:

LOAD

EmployeeID,

Name,

Promotion_Dt,

Mgr_Agr_Dt,

IF(ApplyMap('MAP_MGR_AGR_REQUIRED', EmployeeID, 'No')='Yes' AND [Mgr_Agr_Dt] = IsNull(),'Non-Compliant','Compliant')) as "Compliance Status"

FROM

Agreements.xls (biff, embedded labels, table is Agreements$);

Regards,
H

Not applicable

Re: Using a field (calculated) from one table in another table

Thank you Hector.
This worked but not with "IsNull". I used Len(Trim(

Appreciate your time.

Regards,
Archana

hector_munoz_in
Valued Contributor

Re: Using a field (calculated) from one table in another table

Yes Archana, you are right, is with Len() how it works... Nice to help you!

Community Browser