Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Thanks a lot guys I have tested both answers above and they work. You have just explain applymap in detail to me.
Best Regards
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'
Thanks Manuel. Bery clear now.
you're welcome