Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Partner
Partner

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
Highlighted
MVP
MVP

Re: Conditional statement in load script

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
Highlighted

Re: Conditional statement in load script

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Partner
Partner

Re: Conditional statement in load script

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

Highlighted
MVP
MVP

Re: Conditional statement in load script

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

Highlighted
Partner
Partner

Re: Conditional statement in load script

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!!

Highlighted
Partner
Partner

Re: Conditional statement in load script

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