Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
didierodayo
Partner - Creator III
Partner - Creator III

Updating 2 table from a 3rd table

Hello,

I am trying to figure this out and need some help. I have 3 table as shown below.

1- I need to load and save table 1 and table 2 to QVD with CODE_DESCRIPTION field from table 3.

Note: The codes on table 3 are on 2 columns so I need to pull the right description for Injury_code in table 1 and save it to QVD then I need to pull the right description for Treatment_code and save to QVD.

Your help is much appreciated

Thanks

    

Table1
Injury_idInjury_codeInjury_date
back4051/1/2016
foot3018/8/2012
headCU98415/20/2016
tummyAB74515/20/2013
Ankle31215/20/2010

    

Table2
doctor_idTreatment_code  Treatment_date
87987TEST15612/5/2011
87988WA6012/6/2010
8798977712/7/2009
8799096312/8/2001
8799121512/9/2002

     

     

Table 3 

Standard code      NumberCode      ALPHA_Code           CODE_DESCRIPTION

Injury_code                312                                                                    SEVERE BACK INJURY         

Injury_code                405                                                                    BLEEDING FOOT

Injury_code                301                                                                    Ankle sprain

Injury_code                                                CU984                               HEADACHE                        

Injury_code                                                AB745                               Cuts and abrasions

Treatment_code                                        TEST156                           Groin strain

Treatment_code                                         WA60                                Stress fractures

Treatment_code          777                                                                   Hamstring strain

Treatment_code           963                                                                  Knee joint injuries                        

Treatment_code           215                                                                  Dental damage

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

map_code:

mapping LOAD

  if(NumberCode = '', ALPHA_Code, NumberCode)  as Code

  ,CODE_DESCRIPTION

INLINE [

    StandardCode,      NumberCode,      ALPHA_Code,           CODE_DESCRIPTION,

    Injury_code,        312,                  ,               SEVERE BACK INJURY       

    Injury_code,        405,                  ,               BLEEDING FOOT

    Injury_code,        301,                  ,               Ankle sprain

    Injury_code,           ,           CU984  ,               HEADACHE                      

    Injury_code,           ,           AB745  ,               Cuts and abrasions

    Treatment_code,           ,           TEST156,               Groin strain

    Treatment_code,        ,           WA60   ,               Stress fractures

    Treatment_code,     777,                  ,               Hamstring strain

    Treatment_code,     963,                  ,               Knee joint injuries                      

    Treatment_code,     215,                  ,               Dental damage

];

Infury:

LOAD Injury_id,

     Injury_code,

     Injury_date,

     ApplyMap('map_code', Injury_code, ':') as Injury_desc

FROM

(ooxml, embedded labels, table is Sheet1);

Treatment:

LOAD doctor_id,

     Treatment_code,

     ApplyMap('map_code', Treatment_code, ':') as Treatment_desc

FROM

(ooxml, embedded labels, table is Sheet2);

Outcome:

Screenshot_1.jpg

The qvw is attached.

View solution in original post

6 Replies
Anonymous
Not applicable

Hi Didier,

Try this code, I have used your tables as Inlines, change them with your tables.

Table3:

LOAD * INLINE [

    StandardCode,      NumberCode,      ALPHA_Code,           CODE_DESCRIPTION,

    Injury_code,        312,                  ,               SEVERE BACK INJURY        

    Injury_code,        405,                  ,               BLEEDING FOOT

    Injury_code,        301,                  ,               Ankle sprain

    Injury_code,           ,           CU984  ,               HEADACHE                       

    Injury_code,           ,           AB745  ,               Cuts and abrasions

    Treatment_code,           ,           TEST156,               Groin strain

    Treatment_code,        ,           WA60   ,               Stress fractures

    Treatment_code,     777,                  ,               Hamstring strain

    Treatment_code,     963,                  ,               Knee joint injuries                       

    Treatment_code,     215,                  ,               Dental damage

];

Desc1:

Mapping Load

    NumberCode,

    CODE_DESCRIPTION

RESIDENT Table3

WHERE StandardCode = 'Injury_code' and Len(NumberCode)>0;

Desc2:

Mapping Load

    ALPHA_Code,

    CODE_DESCRIPTION

RESIDENT Table3

WHERE StandardCode = 'Injury_code'  and Len(ALPHA_Code)>0;

Desc3:

Mapping Load

    NumberCode,

    CODE_DESCRIPTION

RESIDENT Table3

WHERE StandardCode = 'Treatment_code' and Len(NumberCode)>0;

Desc4:

Mapping Load

    ALPHA_Code,

    CODE_DESCRIPTION

RESIDENT Table3

WHERE StandardCode = 'Treatment_code'  and Len(ALPHA_Code)>0;

Table1:

LOAD * INLINE [

    Injury_id,    Injury_code,    Injury_date

    back,         405,             1/1/2016

    foot,         301,             8/8/2012

    head,         CU984,             15/20/2016

    tummy,         AB745,             15/20/2013

    Ankle,         312,             15/20/2010

];

Table2:

LOAD * INLINE [

    doctor_id, Treatment_code,   Treatment_date

    87987, TEST156, 12/5/2011

    87988, WA60, 12/6/2010

    87989, 777, 12/7/2009

    87990, 963, 12/8/2001

    87991, 215, 12/9/2002

];

Table1Final:

LOAD

    *,

    ApplyMap('Desc1',Injury_code,ApplyMap('Desc2',Injury_code))    as CODE_DESCRIPTION_INJURY

RESIDENT Table1;

Table2Final:

LOAD

    *,

    ApplyMap('Desc3',Treatment_code,ApplyMap('Desc4',Treatment_code))    as CODE_DESCRIPTION_TREATMENT

RESIDENT Table2;

DROP TABLES Table1, Table2, Table3;

Kind Regards

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

map_code:

mapping LOAD

  if(NumberCode = '', ALPHA_Code, NumberCode)  as Code

  ,CODE_DESCRIPTION

INLINE [

    StandardCode,      NumberCode,      ALPHA_Code,           CODE_DESCRIPTION,

    Injury_code,        312,                  ,               SEVERE BACK INJURY       

    Injury_code,        405,                  ,               BLEEDING FOOT

    Injury_code,        301,                  ,               Ankle sprain

    Injury_code,           ,           CU984  ,               HEADACHE                      

    Injury_code,           ,           AB745  ,               Cuts and abrasions

    Treatment_code,           ,           TEST156,               Groin strain

    Treatment_code,        ,           WA60   ,               Stress fractures

    Treatment_code,     777,                  ,               Hamstring strain

    Treatment_code,     963,                  ,               Knee joint injuries                      

    Treatment_code,     215,                  ,               Dental damage

];

Infury:

LOAD Injury_id,

     Injury_code,

     Injury_date,

     ApplyMap('map_code', Injury_code, ':') as Injury_desc

FROM

(ooxml, embedded labels, table is Sheet1);

Treatment:

LOAD doctor_id,

     Treatment_code,

     ApplyMap('map_code', Treatment_code, ':') as Treatment_desc

FROM

(ooxml, embedded labels, table is Sheet2);

Outcome:

Screenshot_1.jpg

The qvw is attached.

didierodayo
Partner - Creator III
Partner - Creator III
Author

Thanks a lot guys I have tested both answers above and they work. You have just explain applymap in detail to me.

Best Regards

Anonymous
Not applicable

Extract of QlikView help:

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

ApplyMap ('map1', 5, 'xxx') returns 'xxx'

ApplyMap ('map1', 1, 'xxx') returns 'one'

ApplyMap ('map1', 5, null( ) ) returns NULL

ApplyMap ('map1', 3, null( ) ) returns 'three'

didierodayo
Partner - Creator III
Partner - Creator III
Author

Thanks Manuel. Bery clear now.

Anonymous
Not applicable

you're welcome