topic Re: Updating 2 table from a 3rd table in QlikView

Updating 2 table from a 3rd table

didierodayo — Fri, 03 Jun 2016 09:28:59 GMT

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_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
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

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

Re: Updating 2 table from a 3rd table

Anonymous — Fri, 03 Jun 2016 09:52:54 GMT

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

Re: Updating 2 table from a 3rd table

MindaugasBacius — Fri, 03 Jun 2016 10:26:05 GMT

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:

The qvw is attached.

Re: Updating 2 table from a 3rd table

didierodayo — Fri, 03 Jun 2016 10:38:11 GMT

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

Best Regards

Re: Updating 2 table from a 3rd table

Anonymous — Fri, 03 Jun 2016 11:38:58 GMT

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'

Re: Updating 2 table from a 3rd table

didierodayo — Fri, 03 Jun 2016 11:50:57 GMT

Thanks Manuel. Bery clear now.

Re: Updating 2 table from a 3rd table

Anonymous — Fri, 03 Jun 2016 12:57:58 GMT

you're welcome