Reverse entry

[#1]

Hi Community,

in the below scenario  where if any document has an reversal enrty, i have to show Yes in a column called Is Reversal and No if it do not contain a reversal entry.

DDOC  (Document No)	RDOC (Reversal Doc)	Is reversal
123	456	Yes
456	123	Yes
789		No
987		No

I have used the below logic, but it is not showing Yes for all reversal entries. can you let me know where Am going wrong.

Map:

Mapping Load

RDOC,

DDOC

FROM

[Test.QVD]

(qvd);

and in main table

If(match(RDOC,Applymap('Map',DDOC)),'Yes','No') as Is reversal

is not working.

Basically, if RDOC is in DDOC i want it as Yes and no if it is not there.

Kindly help me here.

[sunny_talwar · Accepted solution]

How about this:

Map:

Mapping Load

RDOC,

'Yes' as Flag

FROM

[Test.QVD]

(qvd);

and in main table

ApplyMap('Map', DDOC, 'No') as Is reversal

[#3]

what a simple solution !!!

Thanks Mate, not sure why I thought so complicated and written such a big logic. 

[#4]

Sunny, Where RDOC is null and DDOC has value, it is showing as YES. where it should come as No.

[sunny_talwar]

Try this:

Map:

Mapping Load

RDOC,

'Yes' as Flag

FROM

[Test.QVD]

(qvd)

Where Len(Trim(RDOC)) > 0;

[#6]

The issue is, for few Document nos, RDOC is empty even though it is a reversal entry.

But i need to get YES for which RDOC is missing, since it is part of reversal entry. 

Is this achievable, could you plz help.

DDOC  (Document No)	RDOC (Reversal Doc)	Is reversal
123		Yes
456	123	No

[sunny_talwar]

I would have thought that this is true now. Can you share a sample where this doesn't work?

[#8]

Sunny, if the table is as below, IsReversal should be as mentioned.

Only the rows 1 to 4 should be YES.

S.No.	DDOC	RDOC	Is Reversal
1	123		Yes
2	321	123	Yes
3	789	987	Yes
4	987	789	Yes
5	111		No
6	222		No
7	222		No
8	444		No
9	555		No
10	666		No

[sunny_talwar]

Try this code:

MappingTable:

Mapping

LOAD RDOC,

  'Yes' as Flag

FROM

Community_221370.xlsx

(ooxml, embedded labels, table is Sheet1);

Table:

LOAD S.No.,

    DDOC,

    RDOC,

    ApplyMap('MappingTable', DDOC, If(Len(Trim(RDOC)) > 0, 'Yes', 'No')) as [IsReversal]

FROM

Community_221370.xlsx

(ooxml, embedded labels, table is Sheet1);

[#10]

Sunny, you logic is working perfectly.

But, say, in future if we get a entry which has a RDOC filled and doesn't have a reversal entry in the data which we posses, it should come as 'No' which is logically correct. So I have have added one more condition to your condition...

MappingTable1:

LOAD RDOC,

  'Yes' as Flag

FROM

Test.xls

Mapping2:

LOAD DDOC,

  'Yes' as Flag

FROM

Test.xls

MainTable:

ApplyMap('MappingTable1', DDOC,

        If(Len(Trim(RDOC)) > 0 and (ApplyMap('MappingTable2', RDOC))='Yes',

             'Yes', 'No')) as [IsReversal]

S.No.	DDOC	RDOC	Is Reversal
1	123		Yes
2	321	123	Yes
3	789	987	Yes
4	987	789	Yes
5	456	654	No

Thanks Sunny.