topic Re: Completing an xRef Table in App Development

Completing an xRef Table

smithc2005 — Sat, 16 Nov 2024 18:22:33 GMT

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

Re: Completing an xRef Table

Saravanan_Desingh — Mon, 13 Jul 2020 22:06:45 GMT

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;

Re: Completing an xRef Table

Saravanan_Desingh — Mon, 13 Jul 2020 22:07:34 GMT

Output.

Re: Completing an xRef Table

smithc2005 — Mon, 13 Jul 2020 22:42:36 GMT

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;

Re: Completing an xRef Table

Saravanan_Desingh — Tue, 14 Jul 2020 02:18:29 GMT

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

Re: Completing an xRef Table

smithc2005 — Tue, 14 Jul 2020 02:22:29 GMT

Yes. My apologies!