Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a situation in which I have two financial balance fields. The first field represents the value prior to the transaction, the second field represents the balance after the transaction.
Unfortunately not every recond has information in these fields and as a result I need to look at the previous records ending balance to get the current records opening balance when that field is empty, and I need to l look at the current records opening balance to get the current records closing balance when the current records closing balance is empty.
Ultimately I'm creating an audit process to ensure that everything is balancing properly. In the example below the first Empty space should find the 7.50 and fill in this record, and the second empty space should find the 5.00 and fill in this record. In the end I will see an issue between record 4 and 5 because the closing balance of 5.00 does not equal the opening balance of 2.50. This is a really simple example, in my data set I have thousands of lines of data that have gaps (sometimes several lines long) and I feel I need to fill in all these records to perform my audit properly.
My issue is, I don't know how to address a situation in which both fields rely on the other field to determine their totals.
Open Close
10.00 10.00
10.00 7.50
Empty 5.00
5.00 Empty
2.50 12.50
I hope I've been able to explain my situation clearly. Any assistance would be much appreciated.
Jeff
Update: fixed my if statements here at the top after rereading this.
Conceptually it would be like this:
Load
If(Open=Null() AND Close<>Null(), Close, Open) as Open,
if(Close=Null() AND Open<>Null(), Open, Close) as Close
From
That would assume everything is on the same table. If it is not, you might need to load the tables as they are, use a left join to attach the one table to the other, then do the above formulas on a subsequent resident load of the newly formed table.
Below is a clip from one of my scripts. It starts on a biometrics table I had to pivot via script formulas and group by (it had millions of rows and went to half a million rows that computers could manage), then it left joins a table with user's gender (relevant to formulas that needed gender field and biometrics fields originally from two different tables), and finishes with a formula to populate BMI where height/weight are present but the BMI was missing. You may notice some Z's in front of some fields, but that was just because I wanted the final field to be the regular name and those initially loaded fields needed to be different field name for the formulas. There are some number ranges to keep out junk data as well.
Biometrics1:
LOAD UserId,
Source as [Biometrics Source],
SUM(if(TestName='Systolic' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Systolic,
SUM(if(TestName='Diastolic' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Diastolic,
SUM(if(TestName='Pulse Rate' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as [Pulse Rate],
SUM(if(TestName='BMI' AND TestValue>10 AND TestValue<150,TestValue, Null())) as ZBMI,
SUM(if(TestName='Fasting' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Fasting,
SUM(if((TestName='NonFasting' OR TestName='Non-Fasting') AND TestValue>1 AND TestValue<1000,TestValue,Null())) as NonFasting,
SUM(if(TestName='HbA1c' AND TestValue>0 AND TestValue<100,TestValue/100,Null())) as HbA1c, //divided by 100 to show as percentage 'unit' in tables
SUM(if(TestName='Total Cholesterol' AND TestValue>1 AND TestValue<5000,TestValue,Null())) as [ZTotal Cholesterol],
SUM(if(TestName='HDL' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as ZHDL,
SUM(if(TestName='LDL' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as ZLDL,
SUM(if(TestName='Triglycerides' AND TestValue>1 AND TestValue<5000,TestValue,Null())) as ZTriglycerides,
SUM(if(TestName='Cholesterol Ratio' AND TestValue>=1 AND TestValue<1000,TestValue,Null())) as [ZCholesterol Ratio],
SUM(if((TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)' OR TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)')
AND TestValue>1 AND TestValue<1000,TestValue,Null())) as [Waist Circumference],
SUM(if((TestName='Body Fat (Male)' OR TestName='Body Fat (Female)') AND TestValue>0 AND TestValue<100,TestValue/100,Null()))
as [Body Fat], //divided by 100 to show as percentage 'unit' in tables
SUM(if((TestName='Waist-To-Hip Ratio (Male)' OR TestName='Waist-To-Hip Ratio (Female)') AND TestValue>0 AND TestValue<100,
TestValue,Null())) as [Waist To Hip Ratio],
SUM(if(TestName='Weight' AND TestValue>40 AND TestValue<750,TestValue,Null())) as Weight,
SUM(if(TestName='HeightFt' AND TestValue>1 AND TestValue<12,TestValue,Null())) as HeightFt,
SUM(if(TestName='HeightIn' AND TestValue>=0 AND TestValue<12,TestValue,
if(TestName='HeightIn' AND TestValue>=12, '-'))) as HeightIn,
SUM(if((TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)') AND TestValue>0 AND TestValue<120,TestValue,Null())) as [Waist Circumference (Male)],
SUM(if((TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)') AND TestValue>0 AND TestValue<120,TestValue,Null())) as [Waist Circumference (Female)],
SUM(if(TestName='Body Fat (Male)' AND TestValue>0 AND TestValue<100,TestValue/100,Null()))
as [Body Fat (Male)], //divided by 100 to show as percentage 'unit' in tables
SUM(if(TestName='Body Fat (Female)' AND TestValue>0 AND TestValue<100,TestValue/100,Null()))
as [Body Fat (Female)], //divided by 100 to show as percentage 'unit' in tables
SUM(if(TestName='Waist-To-Hip Ratio (Male)' AND TestValue>0 AND TestValue<10, TestValue,Null())) as [Waist To Hip Ratio (Male)],
SUM(if(TestName='Waist-To-Hip Ratio (Female)' AND TestValue>0 AND TestValue<10, TestValue,Null())) as [Waist To Hip Ratio (Female)],
TimeStamp(TestDate) as TestDate,
Year(Date(TestDate)) as TestYear,
Month(Date(TestDate)) as TestMonth
// Date(Round(ImportedDate,.00001)) as ImportedDate,
// Year(Date(ImportedDate)) as ImportedYear
//After extensive QA ImportedDate seemed to solve one unique small problem while causing other larger issues with flattening the table and selecting records
FROM
(qvd) WHERE Year(TestDate)>2007 AND TestDate<=Now() AND TestName<>'Glucose'
Group By UserId, Source, TimeStamp(TestDate), Year(Date(TestDate)), Month(Date(TestDate));
left join
LOAD UserId,
if(AttributeName='Gender' AND (AttributeValue='M' OR AttributeValue='m'), Dual('Male','Male'),
if(AttributeName='Gender' AND (AttributeValue='F' OR AttributeValue='f'), Dual('Female','Female'),
if((AttributeName='Gender' AND AttributeValue<>'M' AND AttributeValue<>'m' AND AttributeValue<>'F' AND AttributeValue<>'f')
OR(AttributeName='Gender' AND AttributeValue=Null()), Dual('Not Specified','Not Specified')))) as ZGender
FROM
(qvd) Where AttributeName='Gender';
Biometrics2:
Load *,
Round(if((ZBMI<8 OR ZBMI=Null()) AND Weight>10 AND Weight<750 AND HeightFt>1 AND HeightFt<10 AND HeightIn<12,
((Weight)/(((HeightFt*12)+HeightIn)*((HeightFt*12)+HeightIn)))*703, ZBMI),.1) as BMI,
Systolic&' / '&Diastolic as [Blood Pressure]
Resident Biometrics1;
Drop Table Biometrics1;
Steve,
Thanks for the response. It's very much appreciated.
I will admit that after studying your response either I didn't understand the answer, or you didn't understand the question.
What you suggested looks very much like what I was trying to do (maybe I'm close) .
I've only been using Qlikview for a few months now, so I suspect that's it's my limited knowledge.
It seems like I need both of these field to populate simultaniously as it loads so that it has the information it need to determine how to populate the next line.
I'm including a snap shot of a real situation. You'll notice in the middle that there a many lines that are blank, in this case I need it to fill in the "Alltrans.ePurseBalance" based on the previous(Alltrans.ePurseNewBalance" and then fill in the Alltrans.ePurseNewBalance based on the current Alltrans.ePurseBalance.
When I do this currently it seems like I can only load one field at a time, and without the other I can't get the results I need.
I hope this helps explain my situation.
Jeff
Sorry, I’m newish to Qlikview too and there are other possible functions that might do the trick. For my approach I had to get the data I wanted in the formulas onto the same row (and grouped by userid, testdate, source). Once it was on the same row, I could just do some math where the formulas looked left and right across the record/row and populate field A based on computations run against field B and C.
For yours you are actually going back one row in time then over one field instead of just over. So you might do previous of the same field, then have it look over from that field. So some expression or piece of script like:
=If("Alltrans.ePurseBalance"=Null() AND previous("Alltrans.ePurseBalance")<>Null(), Alltrans.ePurseNewBalance)
Or in script:
If("Alltrans.ePurseBalance"=Null() AND previous("Alltrans.ePurseBalance")<>Null(), Alltrans.ePurseNewBalance) as Alltrans.ePurseBalance
So you’d be looking back to the record of the previous epursebalance, and pulling the epursenewbalance value from that record.
I think I have the logic, but syntax is always a bear. ☺ Gysbert!
If the source table is already sorted correctly you can try something like:
load
FieldA, FieldB,...and other fields here...,
if(len(trim(Open))=0,peek(Close),Open) as Open,
if(len(trim(Close))=0,peek(Close), Close) as Close
from ...somewhere...;