Check all available Values

[sumitratnani]

Hi Guys

I have one requirement where i need to create status field using below logic.

Where i have to mark  OPPTY status as 'Correct' if all the possible combination of LEAD OPPTY+OPPTY+INT NO from table A matches with Table B, if any one combination is missing from A in B it will be marked as 'INCORRECT'

So in Belwo example OPPTY ABC0000111 and PQR000012 will be marked as INCORRECT, while OPPTY XYZ000011 will be marked CORRECT

Table A

LEAD OPPTY	OPPTY	INT NO
ABC001	ABC0000111	1
ABC001	ABC0000111	2
ABC001	ABC0000111	5
XYZ001	XYZ000011	3
XYZ001	XYZ000011	4
PQR112	PQR000012	2
PQR112	PQR000012	3
PQR112	PQR000012	4

Header 1	Header 2	Header 3
ABC001	ABC0000111	1
ABC001	ABC0000111	3
ABC001	ABC0000111	4
XYZ001	XYZ000011	3
XYZ001	XYZ000011	4
XYZ001	XYZ000011	5

Thanks

[petter]

Depending on whether you want to have this done in the load script precalculated or mostly in the charts directly I have two suggetions for you.

1) MINMAL PRE-CALULATION IN THE LOAD SCRIPT:

T1: 
LOAD 
 *,
 [LEAD OPPTY]&'|'&OPPTY&'|'&[INT NO] AS Key 
INLINE [
LEAD OPPTY OPPTY INT NO
ABC001 ABC0000111 1
ABC001 ABC0000111 2
ABC001 ABC0000111 5
XYZ001 XYZ000011 3
XYZ001 XYZ000011 4
PQR112 PQR000012 2
PQR112 PQR000012 3
PQR112 PQR000012 4
] (delimiter is \t); 

T2: 
LOAD 
 *,
 [Header 1]&'|'&[Header 2]&'|'&[Header 3] AS Key 
INLINE [
Header 1 Header 2 Header 3
ABC001 ABC0000111 1
ABC001 ABC0000111 3
ABC001 ABC0000111 4
XYZ001 XYZ000011 3
XYZ001 XYZ000011 4
XYZ001 XYZ000011 5
] (delimiter is \t);

Then create a straight table and add one dimension OPPTY and an expression =If(Count([LEAD OPPTY])=Count([Header 1]),'Correct','Incorrect') and you should get this:

A variation is that you could also keep just one table by doing a left join in the load script:

T1: 
LOAD 
 *,
 [LEAD OPPTY]&'|'&OPPTY&'|'&[INT NO] AS Key 
INLINE [
LEAD OPPTY OPPTY INT NO
ABC001 ABC0000111 1
ABC001 ABC0000111 2
ABC001 ABC0000111 5
XYZ001 XYZ000011 3
XYZ001 XYZ000011 4
PQR112 PQR000012 2
PQR112 PQR000012 3
PQR112 PQR000012 4
] (delimiter is \t); 

LEFT JOIN

T2: 
LOAD 
 *,
 [Header 1]&'|'&[Header 2]&'|'&[Header 3] AS Key 
INLINE [
Header 1 Header 2 Header 3
ABC001 ABC0000111 1
ABC001 ABC0000111 3
ABC001 ABC0000111 4
XYZ001 XYZ000011 3
XYZ001 XYZ000011 4
XYZ001 XYZ000011 5
] (delimiter is \t);

Just added a LEFT JOIN on line number 17. The straight table would still be exactly the same.

2) PRE-CALULATION IN THE LOAD SCRIPT:

T1: 
LOAD 
 *,
 [LEAD OPPTY]&'|'&OPPTY&'|'&[INT NO] AS Key 
INLINE [
LEAD OPPTY OPPTY INT NO
ABC001 ABC0000111 1
ABC001 ABC0000111 2
ABC001 ABC0000111 5
XYZ001 XYZ000011 3
XYZ001 XYZ000011 4
PQR112 PQR000012 2
PQR112 PQR000012 3
PQR112 PQR000012 4
] (delimiter is \t); 

LEFT JOIN

T2: 
LOAD 
 *,
 [Header 1]&'|'&[Header 2]&'|'&[Header 3] AS Key 
INLINE [
Header 1 Header 2 Header 3
ABC001 ABC0000111 1
ABC001 ABC0000111 3
ABC001 ABC0000111 4
XYZ001 XYZ000011 3
XYZ001 XYZ000011 4
XYZ001 XYZ000011 5
] (delimiter is \t);

T:
LOAD 
  OPPTY,
  If( Count([LEAD OPPTY]) = Count([Header 1]) , 'Correct' , 'Incorrect' ) AS [OPPTY STATUS]
RESIDENT
  T1
GROUP BY
  OPPTY;

DROP TABLE T1;

The first 31 lines are exactly the same as the previous solution. The lines from 33 to the end are just summarizing the calculation in the load script and dropping the initial table. This is what you should do if you dont need the intial table(s) for further analysis and only the summary result table.

It can similarly be displayed in a straight table but with an expression of just [OPPTY STATUS] or Only([OPPTY STATUS]) (which is the same):

[eduardo_dimperio]

Hi, If i understand right you can do that:

//************************************************

AUX:

LOAD

*

FROM

[TABLE A]

INNER JOIN(AUX)

LOAD

[HEADER 1] AS [LEAD OPPTY],

[HEADER 2] AS [OPPTY],

[HEADER 3] AS [INTO NO],

'CORRECT' AS RESULT

FROM [TABLE B]

//**********************************************

LEFT JOIN ([TABLE A])

LOAD

*

RESIDENT AUX;

DROP TABLE AUX

//**********************************************