Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

using 1 description field for 2 different ID fields

Hi experts

i want to connect 2 fields to 1 description field like below:

table1:

select id,

stateofrecord as table1_stateofrecord

from a;

table2:

select id,

stateofrecord as table2_stateofrecord

from b;

load * inline

[ stateofrecord ,stateofrecord _desc

1,a

2,b

3,c

];

i want something like both of table1_stateofrecord  and table2_stateofrecord  connect to stateofrecord _desc

i use ApplyMap() but i can't achieve to what i  want.can anyone help me?

best regards

5 Replies
datanibbler
Champion
Champion

Hi behnaz,

can you specify a bit more what you want as output?

What form does the ID you get from both tables take? Is that like 1,2,3...?

The only thing I can think of right now, though it might sound stupid:

When you want to use ApplyMap() in those two LOAD statements, you have to load that inline table first.

Best regards,

DataNibbler

abeyphilip
Creator II
Creator II

Hi Behanz,

Are you trying Applymap like this:

Status_Map:

Mapping load * inline

[ stateofrecord ,stateofrecord _desc

1,a

2,b

3,c];

table1:

select id,

stateofrecord as table1_stateofrecord

from a;

table2:

select id,

stateofrecord as table2_stateofrecord

from b;

table1_res:

Noconcatenate load id,

[table1_stateofrecord],

applymap('Status_Map',trim([table1_stateofrecord]),'NA' ) as tab1_Status_Desc

resident table1;

table2_res:

Noconcatenate load id,

[table2_stateofrecord],

applymap('Status_Map',trim([table2_stateofrecord]),'NA' ) as tab2_Status_Desc

resident table2;

Regards,

Abey

Not applicable
Author

i have 2 tables that have a field with name 'state_of_record' in table 1 it is as  state_of_record1 and in table2 it is as state_of_record2, this field shows State of each records in these tables the value of this field is 1,2,3,5 :

1 means the record is on progress.

2 means the record is completed

3 means the record is Completed and Submitted

5 means the record is Confirmed

these description are common for state_of_record1 and state_of_record2.

actually  state_of_record1  and state_of_record2 have same nature and shows state of a record.

i want to know can i have a description field that both of 'state_of_record1' and 'state_of_record2'  use it?

maybe it is such a ridiculous question but i want to know is there a way for doing this?

Best regards

amars
Specialist
Specialist

Hi Behnaz,

You can not use the same description table for both Table1 & Table2 since it will form a loop otherwise. You will need to use them separately

You can use it in the same way as given by Abey just a little ammendment

Status_Map:
load * inline

[ stateofrecord ,stateofrecord _desc

1,a

2,b

3,c

];


table1:

select id,

stateofrecord as table1_stateofrecord,
applymap('Status_Map',trim(stateofrecord),'NA' ) as tab1_Status_Desc

from a;


table2:

select id,

stateofrecord as table2_stateofrecord,
applymap('Status_Map',trim(stateofrecord),'NA' ) as tab1_Status_Desc

from b;

Thanks...

Not applicable
Author

Hi Abey

thanks for your reply i just use applymap() another way.your answer is helpful for me.

best regards.