Conditional statement in load script

[mijileee]

Hello,

I'm having some trouble on how to do this-

I have credit and debit amounts that are linked to a reference number. When the reference number is 3, the debit and credit amounts show beginning balances. When the reference number is any other number it shows the other debit and credits balances.

I want to create a field called "Beginning Balance" that filters the reference number to 3 to show the beginning balances for credits and debits. Then, I want to create fields for the debit amounts and credit amounts that exclude reference number 3 to leave beginning balances out of those fields.

Since this condition affects both the debit and credit field, I'm not sure how to form my if statement in my data load editor.

Credit -gld_cr_amount

Debit -gld_dr_amount

Ref Number- gld_ref_no

Any help would be greatly appreciated! 

[Anil_Babu_Samineni]

Not sure, I understand the request. Perhaps this?

Load Field,
If(gld_ref_no=3, Min(gld_cr_amount)+Min(gld_dr_amount)) as "Beginning Balance"
Resident Table Group By Field;

Or

Load Field,
If(gld_ref_no=3, RangeMin(gld_cr_amount, gld_dr_amount)) as "Beginning Balance"
Resident Table;

[mijileee]

Hi,

Thanks for the reply! However, I don't think using the min() function will bring the correct entries.

To clarify, this table below is what I'm trying to create. 

The Opening Balance column should contain the values of the debit and credit amounts with a reference code=3, while the debit and credit columns should contain all amounts when reference code <>3.

So, I need to somehow combine the debit and credit column into one to create the opening balance field and only display values where reference number = 3. Then I need to create a debit column and credit column where reference number <>3 to include all other amounts.

[jonathandienst · Accepted solution]

Something like this:

LOAD gld_ref_no,
	gld_cr_amount as openingCr,
	gld_dr_amount as openingDr
From ...
Where refNUmber = 3;

Join gld_ref_no,
	refNumber,
	gld_cr_amount as credit,
	gld_dr_amount as debit
From ...
Where refNumber <> 3;

Optionally Sum the credit and debit in the second LOAD (add a Group By statement for the other fields).

[mijileee]

Thank you! This is more along the lines of what I was looking for.

I want to combine the [Opening Credit] and [Opening Debit amount] into one column to create the [Opening Balance Amount] since every entry is either a debit or a credit. I tried concatenating the two fields together but it skewed my results. Any suggestion on how to make this possible?

Thank you!!

[mijileee]

I implemented the script you suggested but for some reason I'm not getting the debit and credit amounts linked to my table.. Not sure why this is happening since the join should have worked?