Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Sales invoices template, debit/credit fields, from other table.

I just finished a Qlikview Developer 1,2 course and learned alot. But there are still problems I face and cannot seem to fix (yet).

I have got a Qlikview template that reads into our financial (record) database. It reads al the debit/credit invoices we have sent/received. For our sales department we use these information. It is the sum(amount) , but this sum(amount) can also be a credit bill, and a credit bill has to be taken off al the debit bills. So the sum(amount) does not give me a good overview of the sales done.

I have created some graphs, and in the right dimensions I use the following expression where I filter out the debit and credit bills "sum(if(DEBCREVOO='1', amount))-sum(if(DEBCREVOO='2', amount))". I would like to have this expression in the load script, because it makes building a set analysis table (almost) impossible. Because the debit and credit invoices are beeing summed up. And that is not the correct way. To make things a bit more difficult, the identifier if a invoice number is credit or debit, which I have to load from another table.

IHFAC3 = the table where all invoices are written to. Which includes a customer identifier, invoice identifier and the AMOUNT

IHFAC0 = has a invoices number and a DEBCREVOO field, which is 1 (for debit) and a 2 (for credit).

So my question is: How can I load the IHFAC3/IHFAC0 from which I don't have to use a condition anymore in the graphs - or other said: more designer side of the story ?

1 Solution

Accepted Solutions
Not applicable

Sales invoices template, debit/credit fields, from other table.

I got it right using your instructions. I am 1 step away from my perfect graph. I am looking for a way to measure the SalesDone. This is should something look like: sum(DebitAmount)-sum(CreditAmount) as SalesDone

I tried making another resident, where in this table, the above expression is done. The dimension should be PER (periode/month) so I can see that amount of sales done / month. I am using the following script,

TEST2:
Load
FAC, JAA, PER, DebitAmount, CreditAmount,
sum(DebitAmount)-sum(CreditAmount) as Gefactureerd
Resident TEST2;
Drop table TEST2;

I get the following error:

Invalid expression
TEST2:
Load
FAC, JAA, PER, DebitAmount, CreditAmount,
sum(DebitAmount)-sum(CreditAmount) as Gefactureerd
Resident TEST2

In each invoice record it is a credit of a debit bill, so I can imagine, that this helps my error message ;(

any clues?

5 Replies
deepakk
Valued Contributor III

Sales invoices template, debit/credit fields, from other table.

hi,

First join the two tables using the invoice no.

Now in the new table use

Load

if(DEBCREVOO =1, amount) as DebitAmount,

if(DEBCREVOO =2, amount) as CreditAmount,

Amount

from newtable;

In the above case you will debit and credit amount seprately and if you want it together you can get it from amount Field.

How to join two tables,

Load

Invoiceno,

Dt,

CustomerNo

from Invoice;

join

Load

Invoiceno,

DEBCREVOO

from Table2.

Make sure that only single records are there in second table for each invoice.

Not applicable

Sales invoices template, debit/credit fields, from other table.

What I dont understand is the part where you make the if) statement FROM new table:

Now I have got:

ODBC CONNECT TO OMN;
LOAD FAC as InvoiceNo, BEDRAG as Amount;
SQL SELECT FAC, BEDRAG
FROM IHFAC03;
JOIN
LOAD FAC as InvoiceNo, if(DEBCREVOO=1, 'Debit') as DebitAmount, if(DEBCREVOO=2, 'Credit') as CreditAmount;
SQL SELECT FAC, DEBCREVOO
FROM DEBHIS00;

How do I merge these two tables, with a string of criteria, where when merged, DEBCREVOO=1 the sum(AMOUNT) is loaded as DebitAmount when DEBCREVOO=2 sum(AMOUNT) is loaded ad CreditAmount? Do I need a resident table or so.

deepakk
Valued Contributor III

Sales invoices template, debit/credit fields, from other table.

hi,

See the Code below.

Test:

LOAD FAC as InvoiceNo, BEDRAG as Amount;
SQL SELECT FAC, BEDRAG
FROM IHFAC03;
JOIN

LOAD FAC as InvoiceNo,

DEBCREVOO;

SQL SELECT FAC, DEBCREVOO
FROM DEBHIS00;

// In below code we are checking if DEBCREVOO value for each invoice, if DEBCREVOO =1 we assign //the amount as Debit amount and if it is 2 we assign the amount as CreditAmount

Load

InvoiceNo,

if(DEBCREVOO =1, amount) as DebitAmount,

if(DEBCREVOO =2, amount) as CreditAmount,

Amount

Resident Test;

Drop table Test



Not applicable

Sales invoices template, debit/credit fields, from other table.

I got it right using your instructions. I am 1 step away from my perfect graph. I am looking for a way to measure the SalesDone. This is should something look like: sum(DebitAmount)-sum(CreditAmount) as SalesDone

I tried making another resident, where in this table, the above expression is done. The dimension should be PER (periode/month) so I can see that amount of sales done / month. I am using the following script,

TEST2:
Load
FAC, JAA, PER, DebitAmount, CreditAmount,
sum(DebitAmount)-sum(CreditAmount) as Gefactureerd
Resident TEST2;
Drop table TEST2;

I get the following error:

Invalid expression
TEST2:
Load
FAC, JAA, PER, DebitAmount, CreditAmount,
sum(DebitAmount)-sum(CreditAmount) as Gefactureerd
Resident TEST2

In each invoice record it is a credit of a debit bill, so I can imagine, that this helps my error message ;(

any clues?

Highlighted
deepakk
Valued Contributor III

Sales invoices template, debit/credit fields, from other table.

hi ,

Since you are using Sum Function, you need to group by it. The best way would be to do it at front end and the script should be

Load
FAC, JAA, PER, DebitAmount, CreditAmount,

if(DEBCREVOO =2, amount*-1,amount) as Amount,


Resident TEST2;

In front end it will Sum(Amount) this will be same as Sum(Debit)- Sum(Credit).

You need not create another resident; you can do this in the above code itself.

I hope you got my point. Also this is how we us sum funtion at script level.

Load

A,

B,

Sum(C)

from table1 group by A, B;