show missing data

[#1]

Hi,

I have 3 tables:

Customers - where i load all customers from data base

Sales Ledger - where i load all info about customer sales data

Payment Ledger - where i load all info about customer payment data

All 3 tables are linked together via CustomerCode that is in all 3 tables.

My problems is that i loose records if customer has an invoice(Sales Ledger table) but has never made a payment(Payment ledger table). And finally calculating all the debts i miss that data because according to qlikview architecture i get only asociative data from all 3 linked tables.

Any body has an idea how i should not loose data in this way.

regards

jja

[MK_QSL]

Join Customer and PaynentLedger Table..

If you load your sample script, apps or example can help you more..

[#3]

Hi Julius

I'm not sure how exactly your debt calculation expression looks like, but maybe try to either aviod selections on columns from Payments table or ignore them using set analysis ({<column_from_payment=>})

Lukasz

[#4]

Hey Julius,

From what you explained the data is not missing in QV, its not available in the data set in the first place.

For example

Customer       Invoice                Amt

123               2345098                 $23

345                4568901                 -

If this is how the data is in the original tables then customer 345 wont have an amount at all. You are not loosing data its just not available.

Thanks

AJ

[#5]

This is my script:

Customers:

load

Text(SL01001) As CustomerCode,

SL01002 As CustomerName,

SL01068 As CreditCode,

SL01060 As DeliveryBlock,

SL01010 As Category,

SL01035 As Salesman,

If(Len(SL01017) = 2,SL01017,If(Len(SL01017)=3,'without',If(Len(SL01017) = 8,Mid(SL01017,7,2),If(Len(SL01017) = 6,Mid(SL01017,5,2),If(Len(SL01017) >= 21,Mid(SL01017,13,2),If(Len(SL01017) = 14,Mid(SL01017,13,2),'without')))))) As CostCentr,

If(Len(SL01017) = 2,'without',If(Len(SL01017)=3,SL01017,If(Len(SL01017) = 9,Mid(SL01017,7,3),If(Len(SL01017) >= 15,'without',If(Len(SL01017)= 14,Mid(SL01017,7,3),'without'))))) As Product,

SL01037 As CreditLimit;

SQL SELECT SL01001, SL01002, SL01068, SL01060, SL01010, SL01035, SL01017, SL01037 FROM SL01$(CompanyNo)00;

SET NULLDISPLAY = 'N';

Payments:

load

Text(SL21001) As CustomerCode,

SL21002 As InvoiceNo,

SL21004 As TransactionNo,

SL21005 As Date,

NUM(SL21005) AS NDATE,

year(SL21005) As Year,

Month(SL21005) As Month,

day(SL21005) As Day,

'Q'& ceil(month(SL21005)/3) as Quarter,

SL21007 As Sum;

SQL SELECT SL21001, SL21002, SL21004, SL21005, SL21007

FROM SL21$(CompanyNo)00 ;

SET NULLDISPLAY = 'N';

Ledger:

load

Text(SL03001) As CustomerCode,

SL03002 As Invoice,

SL03036 As OrderNo,

SL03004 As InvoiceDate,

Year(SL03004) As HYear,

SL03006 As DueDate,

SL03055 As LastPaymentDate,

SL03013 As InvoiceAmmount,

SL03053 As PayedAmmount,

SL03013-SL03053 As Balance,

SL03006-SL03004 As PaymentTerms,

Round(if(SL03055='1900.01.01' or (SL03013-SL03053)<>0, Date(Now()-(SL03006)),(SL03055-SL03006))) As Fact,

if((SL03013-SL03053)>0 and Round(if(SL03055='1900.01.01' or (SL03013-SL03053)<>0, Date(Now()-(SL03006)),(SL03055-SL03006)))>0,'Overdue',if((SL03013-SL03053)>0 and Round(if(SL03055='1900.01.01' or (SL03013-SL03053)<>0, Date(Now()-(SL03006)),(SL03055-SL03006)))<0,'NotDue',if((SL03013-SL03053)>0 and Round(if(SL03055='1900.01.01' or (SL03013-SL03053)<>0, Date(Now()-(SL03006)),(SL03055-SL03006)))=0,'Today','Closed'))) As Condition;

SQL SELECT SL03001, SL03002, SL03004 , SL03006, SL03055, SL03013, SL03036, SL03053 FROM SL03$(CompanyNo)00;

SET NULLDISPLAY = 'N';

in one of my chart box expresions for total Debts count i simply use =Sum([Balance]) but as mentioned the record coresponding to custoemr code is not available in payments table then i loose the SL03013-SL03053 As Balance record for that customer as well though this record in the Ledger table definetely exists

[#6]

So in a few cases customer code is present in Customer and Ledger table but not in Payments table. Still since all three tables are associated through Customer Code you should be able to see the Balance for that customer.

Does your data model look like this:

[#7]

Yes Data Model look like your example. But i don';t see the balance for that customer in my balance Chart. The only way to get  that record visible is to select that customer in selection box then i get the data and the total payed amount is 0 and his exact debt but if i just clear all selections go to my total sheet where i load customers code customer name and Balance - this customer is not visable and there fore i lose the Balance in Total

[#8]

Possible to attach a sample file.

[#9]

This is my sample. I have decreased the number of customers to 3.

As you can see the customercode = 300920539 is ok because it exists in all 3 tables but other 2 customers(as you see in the first sheet customers thei are inactive) thei are missing in Payments table and if i just clear all an go to the last sheet Statistics i can see only the debts for that good customer but I'm missing the debts for those 2 bad customers. If i clear everything and in the first sheet customers select that bad customer for example(302448051) then in the last sheet Statistics I'm able to see him. But i want it to act diferent. I ewant to clear and in the last sheet i want to see all info about customers debts. Hope this time i made it more clearly.

thks

jja

[#10]

I commented out your 'AfterSelect' macro in Document Properties > Triggers > Document Event Trigger > OnAnySelect and its working fine.

I dunno what your AfterSelect macro does (couldn't see it cause of security) but that was causing it.

Check out the attached.

Thanks

AJ