exist function does not work

[#1]

Hi all,

I am missing something here. The exist function works correctly when I use the same fieldname in both tables, but when I try to use when referring to another field with "where Exists(PostingDate, 'Month to close')" it does not work. Any ideas?

LOAD [Month to close] // as PostingDate  --> If I rename it, the exist function works!

FROM

Month End Closing Process.xlsx //modified

(ooxml, embedded labels, table is [Month to close]);

LOAD //PostingDate as [Month to close],

          PostingDate,

  AmountOriginal   

FROM

CF_Data.qvd // (!!! modified)

(qvd)

where Exists(PostingDate, 'Month to close')

thanks a lot!

[sridhar240784]

Hi

It should be like this.Exists(Frist Table Field Name,Second Table Field Name)

                     where Exists('Month to close',PostingDate)

-Sridhar

[SunilChauhan]

try this

LOAD [Month to close] as PostingDate  --> If I rename it, the exist function works!

FROM

Month End Closing Process.xlsx //modified

(ooxml, embedded labels, table is [Month to close]);

LOAD PostingDate as [Month to close],

          PostingDate,

  AmountOriginal  

FROM

CF_Data.qvd // (!!! modified)

(qvd)

where Exists(PostingDate, [Month to close])

[gandalfgray · Accepted solution]

As Sridhar said the first fieldname in the Exists() should already be available before the loading of the current statement, the second fieldname is used when the field to compare in the current load is named something else than what is already loaded.

use:

where Exists([Month to close],PostingDate)

and in case you also want to associate the fields with eachother the complete statements would be:

LOAD [Month to close]

FROM

Month End Closing Process.xlsx

(ooxml, embedded labels, table is [Month to close]);

LOAD PostingDate as [Month to close],

         AmountOriginal   

FROM

CF_Data.qvd

(qvd)

where Exists([Month to close],PostingDate);

/gg