Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
smithc2005
Contributor III
Contributor III

Completing an xRef Table

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

NAMETICKET
ChrisA1
Sue

B2

SusanC3
MikeD4
BobE5
StevenF6
JenG7
CathyH8

 

xREF (incomplete)

FROMNAMEFROMTICKETTONAMETOTICKET
ChrisA1DonaldA1
SusanC3ChristinaC3
BobE5GeorgeE5

 

I need to end up with this:

FROMNAMEFROMTICKETTONAMETOTICKET
ChrisA1DonaldA1
SusanC3ChristinaC3
BobE5GeorgeE5
SueB2SueB2
MikeD4MikeD4
StevenF6StevenF6
JenG7JenG7
CathyH8CathyH8

 

 

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

Labels (1)
  • xREF

3 Solutions

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

smithc2005
Contributor III
Contributor III
Author

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;

 

View solution in original post

Saravanan_Desingh

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

View solution in original post

5 Replies
Saravanan_Desingh

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.

commQV42.PNG

smithc2005
Contributor III
Contributor III
Author

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

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

smithc2005
Contributor III
Contributor III
Author

Yes. My apologies!