Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

sum with left join and date

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 DateQuantity
081501.01.1110
081502.01.115
081504.01.1110


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

Posting DateItem No.Inventory
01.01.11081510
02.01.11081515
03.01.11081515
04.01.11081525


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 DateItem NoInventoryFromDate
ToDate
04.01.11081501.01.1104.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

1 Solution

Accepted Solutions
MVP
MVP

Re: sum with left join and date

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.

3 Replies
MVP
MVP

Re: sum with left join and date

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
Contributor II

sum with left join and date

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.

Not applicable

Re: sum with left join and date

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

Community Browser