topic Re: Last Sale Date As Per The Credit Note Date in QlikView

Last Sale Date As Per The Credit Note Date

ajithsachin — Thu, 16 Jun 2016 11:28:11 GMT

Hi All,

I have two tables, ie; Sales and credit note data. I need to make a report that should show credit note details of the item batch with when it sold last time.(Last sold date should be less than credit note date)

Below i am attaching my sample table structure with data.

Sales:-

InvNum	Date	Item	Batch	Qty	Value
1	01-04-2016	item1	batch1	10	100
2	10-04-2016	item1	batch1	10	100
3	13-04-2016	item1	batch1	5	50

Credit Note:-

CreditNoteNum

Date

Item

Batch

Qty

Value

03-04-2016

item1

batch1

12-04-2016

item1

batch1

15-04-2016

item1

batch1

and my expected result table is something like this below.

Item	Batch	CreditNote Date	Last SaleDate
item1	batch1	03-04-2016	01-04-2016
item1	batch1	12-04-2016	10-04-2016
item1	batch1	15-04-2016	13-04-2016

Can anybody help please..

Regards,

Ajith

Re: Last Sale Date As Per The Credit Note Date

swuehl — Thu, 16 Jun 2016 11:35:52 GMT

You can create a date interval from your CreditNote dates:

Creating a Date Interval from a Single Date

Then use INTERVALMATCH LOAD prefix to link your credit notes to the sales dates, using Item and batch as keys:

IntervalMatch

Re: Last Sale Date As Per The Credit Note Date

sunny_talwar — Thu, 16 Jun 2016 12:09:49 GMT

Does Qty and Value also take a part in knowing how the two tables need to join together?

Re: Last Sale Date As Per The Credit Note Date

ajithsachin — Thu, 16 Jun 2016 12:30:44 GMT

It is actually inside the FACT table. Qty we can change to Sales_Qty and Return_Qty and value will be Sales_Value and Return_Value.

So that Fact will look like this

Re: Last Sale Date As Per The Credit Note Date

sunny_talwar — Thu, 16 Jun 2016 12:32:13 GMT

So currently it looks like this? but you want them to be on the same line? Is that the goal?

Re: Last Sale Date As Per The Credit Note Date

ajithsachin — Thu, 16 Jun 2016 12:36:50 GMT

i want to show a report like same item same batch on which date credit note happened and in next column when was that batch sold last time.(max of sales date where sale date less than equal to credit note date)

May be something like this below.