Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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$]);
Hi Priya,
See the attched application
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;
Hi Priya,
PFA attachment i hope that will solve ur problem.
Thanks