New field conditional on two variables

[#1]

Hi all,

I am working a creating a new field that is conditional on two variables. There are the following fields:

1) Invoice (INVOICE)

2) Debit/Credit Indicator (DEB_CRED) - has two values, H or S

3) Line Item Number (LINE_NUM)

4) Document Type (DOC_TYPE)

What I need is a new Debit Credit Indicator where I change some of the values. In each invoice there are a number of lines with various DEB_CRED depending on what is relevant. For this new field that I need, I generally need the DEB_CRED of the first LINE_NUM to be transferred to all other lines on that INVOICE. So if line 5 says H but line 1 says S, then I want line 5 in the new field to follow line 1 and say S. However, to complicate matters I have one particular DOC_TYPE where I don't want my new field to change any values, but remain as in the original field. Let's call this DOC_TYPE  "KA".

I am guessing that I need a load statement with some sort of IF expression, but am open to other suggestions. Something along the lines of:

IF DOC_TYPE = KA

THEN NEW_FIELD_NAME_DEB_CRED_2,                  (if not)

DEB_CRED should display same value as LINE_NUM 1.

Or maybe a Load where

DEB_CRED as DEB_CRED_2 where DOC_TYPE = KA and IF DOC_TYPE <> KA, then fill in same value as LINE_NUM 1 for all remaining values.

Can someone help me with scripting this?

[Gysbert_Wassenaar]

I'm going to assume your data source is ordered by invoice and line number already. If it isn't then you first need to load to load the unsorted data and then use a resident load to order the data.

You can use the Previous and Peek functions. Something like:

LOAD

     INVOICE,

     DEB_CRED,

     LINE_NUM,

     DOC_TYPE,

     IF(INVOICE = Previous(INVOICE) AND DOC_TYPE <> 'KA', peek('DEB_CRED2'), DEB_CRED) as DEB_CRED2

FROM ...

[#3]

It worked perfectly! Thank you so much.