sum with left join and date

[#1]

Dear guys,

very simple question I thought. Here's what I want to do.

I have a "LedgerEntry" table which looks like this:

Item No.	Posting Date	Quantity
0815	01.01.11	10
0815	02.01.11	5
0815	04.01.11	10

I want to make a new table "InventoryEntry" in the script that represents the following

Posting Date	Item No.	Inventory
01.01.11	0815	10
02.01.11	0815	15
03.01.11	0815	15
04.01.11	0815	25

Inventory is the sum of all ledger entries until that date.

I already managed to create a table that contains an entry for each "Posting Date" and each "Item No.". Now I want to join the sum (Quantity) in that table as Inventory. I thought it would be something like this:

LEFT JOIN (InventoryEntry) LOAD

  sum(Quantity) AS Inventory 

resident LedgerEntry WHERE [Posting Date] <= InventoryEntry.[Posting Date]

Haha. Nice try. I cannot make a reference between the two tables after the WHERE.

So I tried to gather some wisdom in the forum and found something about the INTERVALMATCH.

I added the fields "FromDate" and "ToDate" to the InventoryEntry Table. It now looks like this:

Posting Date	Item No	Inventory	FromDate	ToDate
04.01.11	0815		01.01.11	04.01.11

But I can't get the the INTERVALMATCH to work. Is INTERVALMATCH the right way to solve this problem? What other options do I have`?

Thanks in advance

Regards

Alex

[swuehl · Accepted solution]

Hi Alex,

I think maybe something like attached might help you, I posted the attached to a similar request some weeks ago.

http://community.qlik.com/thread/35152

I just added a cumulative value for you (last join in script).

Hope this helps,

Stefan

P.S. You could probably use interval match for that too, but you also need to accumulate the values then.

[tanelry]

There was similar question recently and I posted a hint for good datamodel here.

Basically you leave the transactions table as is and create "date link" between this and calendar table. Then you get inventory in charts using calendar dimensions and sum(Quantity) as expression.

[#4]

Thank you very much. The last join did it.

I would have never ever found out that previous and rangesum would solve the problem.

How many years of QV experience does it take to find smth. like that  out? 

Thanks again and kind regards

Alex