Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an xREF table that I am trying to complete because unless it is, my data won't make it through the xREF (bridge).
As an example, the xREF table has the "from" name and "to" name, along with the Ticket. Since it is incomplete, I need rows referring to the ones not being changed.
My concept script is below as well.
Sample Data
Beginning
NAME | TICKET |
Chris | A1 |
Sue | B2 |
Susan | C3 |
Mike | D4 |
Bob | E5 |
Steven | F6 |
Jen | G7 |
Cathy | H8 |
xREF (incomplete)
FROMNAME | FROMTICKET | TONAME | TOTICKET |
Chris | A1 | Donald | A1 |
Susan | C3 | Christina | C3 |
Bob | E5 | George | E5 |
I need to end up with this:
FROMNAME | FROMTICKET | TONAME | TOTICKET |
Chris | A1 | Donald | A1 |
Susan | C3 | Christina | C3 |
Bob | E5 | George | E5 |
Sue | B2 | Sue | B2 |
Mike | D4 | Mike | D4 |
Steven | F6 | Steven | F6 |
Jen | G7 | Jen | G7 |
Cathy | H8 | Cathy | H8 |
The script I tried but will not work. I can't figure this out conceptually.
BEG:
LOAD
NAME,
TICKET,
NAME & TICKET as NAMETICKET_PRE,
NAME & TICKET as NAMETICKET_PRE_TEST
FROM [lib://***/BEG.xlsx]
(ooxml, embedded labels, table is Sheet1);
XREF:
LOAD
FROMNAME & FROMTICKET as NAMETICKET_PRE,
TONAME & TOTICKET as NAMETICKET_POST
FROM [lib://*****/XREF.xlsx]
(ooxml, embedded labels, table is Sheet1);
CONCATENATE(XREF)
LOAD
NAMETICKET_PRE,
NAMETICKET_PRE as NAMETICKET_POST
Resident BEG
WHERE NOT(EXISTS(NAMETICKET_PRE, NAMETICKET_PRE_TEST));
One solution is.
BEG:
LOAD RowNo() As RowID, * INLINE [
NAME, TICKET
Chris, A1
Sue, B2
Susan, C3
Mike, D4
Bob, E5
Steven, F6
Jen, G7
Cathy, H8
];
XREF:
Mapping
LOAD FROMNAME&'@@'&FROMTICKET, TONAME&'@@'&TOTICKET;
LOAD * INLINE [
FROMNAME, FROMTICKET, TONAME, TOTICKET
Chris, A1, Donald, A1
Susan, C3, Christina, C3
Bob, E5, George, E5
];
OUT:
LOAD RowID, NAME As FROMNAME, TICKET As FROMTICKET,
SubField(ApplyMap('XREF',NAME&'@@'&TICKET,NAME&'@@'&TICKET),'@@',1) As TONAME,
SubField(ApplyMap('XREF',NAME&'@@'&TICKET,NAME&'@@'&TICKET),'@@',2) As TOTICKET
Resident BEG;
Drop Table BEG;
Thank You for the Applymap idea!! This is the first time I've had a use for it.
Solution:
BEG:
LOAD
NAME,
TICKET,
NAME & TICKET as NAMETICKET_PRE
FROM [****/BEG.xlsx]
(ooxml, embedded labels, table is Sheet1);
XREF_PRE:
MAPPING
LOAD
FROMNAME & FROMTICKET as NAMETICKET_PRE,
TONAME & TOTICKET as NAMETICKET_POST
FROM [lib://***/IDEATEST/XREF.xlsx]
(ooxml, embedded labels, table is Sheet1);
XREF:
LOAD
NAMETICKET_PRE,
ApplyMap('XREF_PRE', NAMETICKET_PRE, NAMETICKET_PRE) as NAMETICKET_POST
Resident BEG;
Nice to hear that, it solves your problem. Are you not going to accept my response as solution? 😀
One solution is.
BEG:
LOAD RowNo() As RowID, * INLINE [
NAME, TICKET
Chris, A1
Sue, B2
Susan, C3
Mike, D4
Bob, E5
Steven, F6
Jen, G7
Cathy, H8
];
XREF:
Mapping
LOAD FROMNAME&'@@'&FROMTICKET, TONAME&'@@'&TOTICKET;
LOAD * INLINE [
FROMNAME, FROMTICKET, TONAME, TOTICKET
Chris, A1, Donald, A1
Susan, C3, Christina, C3
Bob, E5, George, E5
];
OUT:
LOAD RowID, NAME As FROMNAME, TICKET As FROMTICKET,
SubField(ApplyMap('XREF',NAME&'@@'&TICKET,NAME&'@@'&TICKET),'@@',1) As TONAME,
SubField(ApplyMap('XREF',NAME&'@@'&TICKET,NAME&'@@'&TICKET),'@@',2) As TOTICKET
Resident BEG;
Drop Table BEG;
Output.
Thank You for the Applymap idea!! This is the first time I've had a use for it.
Solution:
BEG:
LOAD
NAME,
TICKET,
NAME & TICKET as NAMETICKET_PRE
FROM [****/BEG.xlsx]
(ooxml, embedded labels, table is Sheet1);
XREF_PRE:
MAPPING
LOAD
FROMNAME & FROMTICKET as NAMETICKET_PRE,
TONAME & TOTICKET as NAMETICKET_POST
FROM [lib://***/IDEATEST/XREF.xlsx]
(ooxml, embedded labels, table is Sheet1);
XREF:
LOAD
NAMETICKET_PRE,
ApplyMap('XREF_PRE', NAMETICKET_PRE, NAMETICKET_PRE) as NAMETICKET_POST
Resident BEG;
Nice to hear that, it solves your problem. Are you not going to accept my response as solution? 😀
Yes. My apologies!