topic Re: Problem with joins in QlikView

Problem with joins

Sat, 27 Jul 2013 15:46:53 GMT

Hello All,

I have this data in transfer table.The values in LocationFrom and LocationTo are from Location Table

MacID	LocationFrom	LocationTo
1	1	2
2	2	3
3	3	4
4
5

I need the output in the below format

MacID	LocationFrom	LocationTo
1	LA	LB
2	LB	LC
3	LC	LD
4
5

Please find the attached qvw and the excel file.

Thanks and Regards,

Priya

Re: Problem with joins

nagaiank — Sat, 27 Jul 2013 17:06:39 GMT

Try the following script and see if it meets your needs.

LocationMap:

Mapping LOAD LocationID,

LocationName

FROM Book1.xlsx (ooxml, embedded labels, table is Sheet2);

Data:

LOAD MacID,

ApplyMap('LocationMap',LocationFrom) as LocationFrom,

ApplyMap('LocationMap',LocationTo) as LocationTo,

TransferDate

FROM Book1.xlsx (ooxml, embedded labels, table is Sheet3);

Re: Problem with joins

Nagaraju_KCS — Sat, 27 Jul 2013 18:00:23 GMT

Hi Priya,

Try this...........

Map:

Mapping LOAD LocationID,

LocationName

FROM

(biff, embedded labels, table is [Sheet2$]);

RESULT:

LOAD MacID,

ApplyMap('Map', LocationFrom) as LF,

ApplyMap('Map', LocationTo) as LT,

Date(TransferDate,'MM/DD/YYYY') as TD

FROM

(biff, embedded labels, table is [Sheet3$]);

join

LOAD MacID,

MacName

FROM

(biff, embedded labels, table is [Sheet1$]);

Re: Problem with joins

Kushal_Chawda — Sat, 27 Jul 2013 19:34:57 GMT

Hi Priya,

See the attched application

Re: Problem with joins

nizamsha — Sun, 28 Jul 2013 03:38:17 GMT

machine:

LOAD * Inline [

macid,macnme

1,aa

2,bb

3,cc

];

location:

Mapping LOAD * Inline [

locid,locname

1,la

2,lb

3,lc

4,ld

5,le

5,lf

6,lg

];

transfer:

LOAD * Inline [

macid,frm,to

1,1,2

2,2,3

3,3,4

4,4,5

5,5,6

];

tr1:

LOAD macid,

applymap('location',frm)as best,

applymap('location',to)as best1

Resident transfer;

DROP Table transfer;

Re: Problem with joins

VishalWaghole — Mon, 29 Jul 2013 06:39:11 GMT

Hi Priya,

PFA attachment i hope that will solve ur problem.

Thanks