topic Re: Mapp tables in QlikView

Mapp tables

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Hey ALL I need to know how I can mapp table 1 and table 2 in the script to get the table 3: HOW YOU CAN HELP THANK YOU

Table 1:
Code	Bucket
MR	B2
MR	B3
HC	B4
HC	B5
HC	B6
HC	B7
HC	B8
HC	B9
WO	B10
WO	B12
WO	B13
Table2 :
Unit CODE	Unit_Desc	Bucket-Code
COLL1	A	MR
COLL2	S	MR
COLL3	D	HC
COLL4	F	HC
COLL5	G	HC
COLL6	H	WO
COLL7	J	WO
COLL11	K	MR
COLL12	L	MR
COLL15	Z	HC
COLL16	X	HC
COLL17	C	MR
COLL18	V	MR
COLL19	N	WO
COLL20	M	WO
COLL21	B	HC
COLL22	Q	MR
COLL24	W	MR
COLL25	E	MR
COLL26	R	MR
COLL27	T	HC
Table3 Ouput:
Unit Code	Unit_Desc	Bucket-Code	Bucket
COLL1	A	MR	B2
COLL1	A	MR	B3
COLL2	S	MR	B2
COLL2	S	MR	B3
COLL11	K	MR	B2
COLL11	K	MR	B3
COLL12	L	MR	B2
COLL12	L	MR	B3
COLL17	C	MR	B2
COLL17	C	MR	B3
COLL18	V	MR	B2
COLL18	V	MR	B3
COLL22	Q	MR	B2
COLL24	W	MR	B2
COLL24	W	MR	B3
COLL25	E	MR	B2
COLL26	R	MR	B3
COLL3	D	HC	B4
COLL3	D	HC	B5
COLL3	D	HC	B6
COLL3	D	HC	B7
COLL3	D	HC	B8
COLL3	D	HC	B9
COLL5	G	HC	B4
COLL5	G	HC	B5
COLL5	G	HC	B6
COLL5	G	HC	B7
COLL5	G	HC	B8
COLL5	G	HC	B9
COLL15	Z	HC	B4
COLL15	Z	HC	B5
COLL15	Z	HC	B6
COLL15	Z	HC	B7
COLL15	Z	HC	B8
COLL15	Z	HC	B9
COLL16	X	HC	B4
COLL16	X	HC	B5
COLL16	X	HC	B6
COLL16	X	HC	B7
COLL16	X	HC	B8
COLL16	X	HC	B9
COLL21	B	HC	B4
COLL21	B	HC	B5
COLL21	B	HC	B6
COLL21	B	HC	B7
COLL21	B	HC	B8
COLL21	B	HC	B9
COLL27	T	HC	B4
COLL27	T	HC	B5
COLL27	T	HC	B6
COLL27	T	HC	B7
COLL27	T	HC	B8
COLL27	T	HC	B9
COLL6	H	WO	B10
COLL6	H	WO	B12
COLL6	H	WO	B13
COLL7	J	WO	B10
COLL7	J	WO	B12
COLL7	J	WO	B13
COLL19	N	WO	B10
COLL19	N	WO	B12
COLL19	N	WO	B13
COLL20	M	WO	B10
COLL20	M	WO	B12
COLL20	M	WO	B13

Re: Mapp tables

Peter_Cammaert — Thu, 28 Sep 2017 12:03:39 GMT

LEFT JOIN (Table2)

LOAD Code As Bucket-Code, Bucket RESIDENT Table1;

RENAME Table Table2 TO Table3;

will do, I guess.

Re: Mapp tables

its_anandrjs — Thu, 28 Sep 2017 12:07:07 GMT

Try this way with mapping load and be insure you have unique values in mapping table.

MapTable:

Mapping

LOAD

Code as Bucket_Code,

Bucket

from Source;

Table2:

Load

[Unit CODE],

Unit_Desc,

Bucket-Code as Bucket_Code,

ApplyMap('MapTable',Bucket-Code,'Not Matched')

from source;

Re: Mapp tables

Peter_Cammaert — Thu, 28 Sep 2017 12:15:12 GMT

Put Bucket-Code between square brackets, or QlikView will turn it into a subtraction. Like

LOAD Code AS [Bucket-Code], ...

Re: Mapp tables

Clever_Anjos — Thu, 28 Sep 2017 12:31:18 GMT

Usefull reading: Don't join - use Applymap instead

Re: Mapp tables

effinty2112 — Thu, 28 Sep 2017 12:31:34 GMT

Hi Mario,

Your output table seems to be missing some lines for example:

Unit CODE	Unit_Desc	Bucket-Code	Bucket
COLL4	F	HC	B4
COLL4	F	HC	B5
COLL4	F	HC	B6
COLL4	F	HC	B7
COLL4	F	HC	B8
COLL4	F	HC	B9

Maybe this:

Output:

LOAD * INLINE [

Unit CODE, Unit_Desc, Bucket-Code

COLL1, A, MR

COLL2, S, MR

COLL3, D, HC

COLL4, F, HC

COLL5, G, HC

COLL6, H, WO

COLL7, J, WO

COLL11, K, MR

COLL12, L, MR

COLL15, Z, HC

COLL16, X, HC

COLL17, C, MR

COLL18, V, MR

COLL19, N, WO

COLL20, M, WO

COLL21, B, HC

COLL22, Q, MR

COLL24, W, MR

COLL25, E, MR

COLL26, R, MR

COLL27, T, HC

];

Inner Join(Output)

LOAD

Code as [Bucket-Code],

Bucket;

LOAD * INLINE [

Code, Bucket

MR, B2

MR, B3

HC, B4

HC, B5

HC, B6

HC, B7

HC, B8

HC, B9

WO, B10

WO, B12

WO, B13

];