Matching one record out of two for an if statement

[#1]

Hi

I have a scenario where I have two different records for one ID in the same field:

Example:     ID               DocumentType

                    12345         Invoice

                    12345         Statement

I am wanting to use an if statement where I say that if the DocumentType = Invoice then do the calculation which is fine for the IDs that only have an Invoice as the Document Type but in the cases where the Document Type for the ID has two different records I am getting a Null value for my calculation. What is the best way to handle this?

Thanks

[nagarjuna_kotha]

Can u explain little bit more what exactly u want with Sample Data.??

[PrashantSangle]

Hi,

what is your calculation expression??

are you using naked expression modify it with aggregation function.

Regards

[sasiparupudi1]

Use a mapping load in your script of an if condition

map

mapping load * inline

[

From ,To

Invoice, Invoice

Statement,Invoice

];

YourFact:

load ID,

map('map',DocumentType,'NA') as DocumentType

from your data source;

this should give you invoice as the document type for all the records

hth

Sasi

[#5 · Accepted solution]

Thanks everyone but no one really understood what I was trying to do. I stuck with it and got it to work in the end and this is how:

I ended up loading an inline table and joining it to my original table with the document types in it and allocating a number to the different types of documents making sure my specific document that I was needing to flag was number 1.

I then allocated each row a flag of either Y or N if it had that specific document that I was looking for and then finally I used the peek function to look and see if each ID number had a Y flag and if it did to then allocate the Y flag to all the rows of that ID number. I had to sort on the ID number as well as the Document Type numbers in order to get it to work properly. So it is a bit of a work around but you can get there in the end