topic Re: Reverse entry in QlikView

Reverse entry

Fri, 17 Jun 2016 12:41:20 GMT

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.

Re: Reverse entry

sunny_talwar — Fri, 17 Jun 2016 12:58:51 GMT

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

Re: Reverse entry

Fri, 17 Jun 2016 13:21:45 GMT

what a simple solution !!!

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

Re: Reverse entry

Fri, 17 Jun 2016 13:39:56 GMT

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

Re: Reverse entry

sunny_talwar — Fri, 17 Jun 2016 13:46:54 GMT

Try this:

Map:

Mapping Load

RDOC,

'Yes' as Flag

FROM

[Test.QVD]

(qvd)

Where Len(Trim(RDOC)) > 0;

Re: Reverse entry

Fri, 17 Jun 2016 14:39:15 GMT

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

Re: Reverse entry

sunny_talwar — Fri, 17 Jun 2016 14:49:50 GMT

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

Re: Reverse entry

Sat, 18 Jun 2016 07:06:41 GMT

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

Re: Reverse entry

sunny_talwar — Sat, 18 Jun 2016 13:47:04 GMT

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

Re: Reverse entry

Sat, 18 Jun 2016 20:08:26 GMT

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.

Re: Reverse entry

sunny_talwar — Sun, 19 Jun 2016 01:27:59 GMT

Not sure I understand how is 321 Yes? 321 is not a RDOC, right?