Completing an xRef Table

[smithc2005]

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));

[Saravanan_Desingh · Accepted 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;

[Saravanan_Desingh]

Output.

[smithc2005 · Accepted solution]

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;

[Saravanan_Desingh · Accepted solution]

Nice to hear that, it solves your problem. Are you not going to accept my response as solution? 😀

[smithc2005]

Yes. My apologies!