topic Re: sum with left join and date in QlikView

sum with left join and date

Fri, 04 Nov 2011 20:29:04 GMT

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

Re: sum with left join and date

swuehl — Fri, 04 Nov 2011 21:52:41 GMT

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.

sum with left join and date

tanelry — Fri, 04 Nov 2011 22:10:26 GMT

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.

Re: sum with left join and date

Sat, 05 Nov 2011 18:29:07 GMT

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