Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
mijileee
Partner - Newbie
Partner - Newbie

Conditional statement in load script

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

 

ahp_error3.png

Any help would be greatly appreciated! 

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mijileee
Partner - Newbie
Partner - Newbie
Author

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.

ahp-error4.PNG

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mijileee
Partner - Newbie
Partner - Newbie
Author

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
Partner - Newbie
Partner - Newbie
Author

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?

ahp_error5.PNG