How to apply XLOOKUP in Alteryx

[Miki2021]

I have to consolidate a sales report by combining two datasets together.

Dataset A: 

Order number	Amount	Currency
100001	10000	USD
100002	20000	USD
100003	10000	USD
100004	20000	USD
100005	10000	USD

Dataset B:

Order number	Order item	Out of stock	Payment issue	Delivery issue
100001	a	Y	Y
100001	b		Y
100001	c	Y
100002	a
100002	b	Y		Y
100003	a
100004	a		Y
100004	b			Y
100005	a	Y

Dataset B is a bit more detailed as it carries information about each order item and the associated order issues.

Historically, I tried to use XLOOKUP to get an output on the orders and order issues, without the order item details. 

Expected outcome: 

Order number	Amount	Currency	Out of stock	Payment issue	Delivery issue
100001	10000	USD	Y	Y	0
100002	20000	USD	Y	0	Y
100003	10000	USD	0	0	0
100004	20000	USD	0	Y	Y
100005	10000	USD	Y	0	0

However, I recognize XLOOKUP has its limitations. Just wonder if there are smarter ways to get the same output by leveraging Alteryx?

[Jean-Balteryx]

Hi @Miki2021 ,

Can you split your different tables across sheets in your file ?

[Luke_C · Accepted solution]

Hi @Miki2021 

Here's an example of how you'd do this:

1. Summarize second table to remove product info (I used MAX to get the correct values, basically if one has a payment issue, delivery issue, etc the Y will be applied)
2. Join the data on order number

[Miki2021]

Yes @Jean-Balteryx , here you go. Hope this is clearer! 

[HomesickSurfer]

Hi @Miki2021 

My simple approach below and attached.

Records merged and summarized.

[Jean-Balteryx]

Thank you @Miki2021  but @Luke_C found the simplest solution so I won't have any value to add here !

[HomesickSurfer]

Hi @Miki2021 

Will any of your values be other than a 'Y'?

[Miki2021]

@Luke_C  Thanks Luke! Good to know about MAX - I didn't know this can be used for string.

Really helpful!

[Miki2021]

@Jean-Balteryx  Indeed! Thanks anyways. I was originally wondering if RegEx could be useful.

Cheers,

Miki

[Jean-Balteryx]

You are welcome !

No REGEX wouldn't have help you here !