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?

[Miki2021]

@HomesickSurfer Thanks a lot for your solution. 

[Miki2021]

@HomesickSurfer The original data is a string. Will numeric value matter?

[HomesickSurfer]

@Miki2021 , you are welcome.  Do let me know if any of your values are other than a 'Y'.  If so, example, 'N/A' or X, etc...a summary using MAX may yield undesirable results - hence use of 'Contains()'

[Miki2021]

@HomesickSurfer Thanks for the reminder! I dun have N/A or X in the field. Do you mean that if there is another string character e.g. A, the MAX will pick up another value?

[Jean-Balteryx]

@Miki2021 the Max will pick up the last value in the alphabetical order. So if you have something that is after "Y" alphabetically, it will be taken instead of "Y".

[HomesickSurfer]

'MAX' returns the highest Alphanumeric value.

MAX of the below returns:  ZZZ

0
1
2
3
A1
1A
C3
3C
YY
Y1
Z
YYY
N/A
ZZ
ZZZ
Z11