topic Re: Re: show missing data in QlikView

show missing data

Thu, 13 Feb 2014 18:47:34 GMT

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

Re: show missing data

MK_QSL — Thu, 13 Feb 2014 18:50:54 GMT

Join Customer and PaynentLedger Table..

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

Re: show missing data

Thu, 13 Feb 2014 18:53:00 GMT

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

Re: show missing data

Thu, 13 Feb 2014 18:55:08 GMT

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

Re: show missing data

Thu, 13 Feb 2014 19:14:55 GMT

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

Re: show missing data

Thu, 13 Feb 2014 19:35:24 GMT

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:

Re: show missing data

Thu, 13 Feb 2014 19:44:49 GMT

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

Re: show missing data

Thu, 13 Feb 2014 20:50:09 GMT

Possible to attach a sample file.

Re: Re: show missing data

Fri, 14 Feb 2014 07:28:25 GMT

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

Re: Re: Re: show missing data

Fri, 14 Feb 2014 15:52:12 GMT

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

Re: Re: Re: Re: show missing data

Mon, 17 Feb 2014 07:30:58 GMT

Hi,

Sorry i forgot to remove Module password. Now it is removed see attached file. Basically what that macro does it sets the vmindate and vmaxdate variable values to empty after i press the clear button. If you select sheet payments and select that good customer and you can see i have 2 calendar objects where users can define the between date of payments which are controlled by variables. I didn't figure it out how to clear those variables after i press the clear button so found this nasty example somewhere and seems that it is messing something that i don't understand now. It should anyway set the date to nothing. Yes if you comment this macro all is working ok except then how should i clear those 2 calandar fields together with button clear?

Re: Re: Re: Re: Re: show missing data

Mon, 17 Feb 2014 16:35:23 GMT

I had a similar requirement and the way i cleared the variables was through a separate button.

Check out the Payments tab. I created a button 'Clear Dates' which needs to be clicked to clear the variables.

Since these are variables i think this would be the best way to approach.

Thanks

Re: show missing data

Tue, 18 Feb 2014 06:44:04 GMT

Hi,

Thanks for response i finally got it to work bit in a bit different way. On the calendar on the date to where it is controled by max date variable i had i macro on input and on the clear event i had another macro which was setting the variables to "" but it was not releasing the field Ndate which acts as a between dates values so i had like an active selection Ndate<=>= and it was causing that error. Thank you for help.

jja