Check all available Values

sumitratnani — Fri, 25 May 2018 06:53:12 GMT

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

ABC001

ABC0000111

ABC001

ABC0000111

XYZ001

XYZ000011

XYZ001

XYZ000011

XYZ001

XYZ000011

Thanks

Re: Check all available Values

petter — Fri, 25 May 2018 12:34:27 GMT

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

Re: Check all available Values

eduardo_dimperio — Fri, 25 May 2018 12:49:19 GMT

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

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

topic Re: Check all available Values in QlikView

Check all available Values

Re: Check all available Values

Re: Check all available Values