Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to "left joining" using some criteria

Hi everybody.

Can't find an answer for this.

I have an invoice table, with this fields:

Number CreationDate Article Quantity

And have a table with costs history of each article, with this fields:

Article FromDate ToDate Cost

I can't find a way to add a cost to each article in the invoice table, taking the valid cost at the moment of the creation of that invoice. This means that (CreationDate >= FromDate AND CreationDate <= ToDate), because each article can have many records in CostHistory table (no overlapping of date is allowed).

How can I do that?

Thanks in advance.

Damian.

24 Replies

Re: How to "left joining" using some criteria

Damian

Looks like you are need of the IntervalMatch() function.

This Blog Post IntervalMatch by hicexplains it well.




Best Regards,     Bill

Not applicable

Re: How to "left joining" using some criteria

Hi Bill.

Mmmhhhh... not sure. I tried it, but it just left me a table with dates, and I need more than that, because of the article.

I tried this way:

IntervalMatchCostArticlesHist:

    IntervalMatch([CreationDate]) Load distinct [FromDate], [ToDate] Resident CostHistory;

What you think?

Regards. Damian.

stabben23
Honored Contributor

Re: How to "left joining" using some criteria

Hi, try this, first min/max on all Items, then Intervallmatch.

MinMaxDate:

Load distinct
Item,

min(Date1) as MinDate,
max(Date2) as MaxDate
resident Your main table

group by Item

order by Item asc


Let vMinDate = Peek('MinDate',0,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',0,'MinMaxDate') ;

Calendar:
Load recno()+$(vMinDate)-1 as Date
Autogenerate vMaxDate - vMinDate;

JOIN (MinMaxDate) INTERVALMATCH (Date) LOAD MinDate, MaxDate
RESIDENT MinMaxDate;

Drop Table Test;
Drop Table Calendar;

Not applicable

Re: How to "left joining" using some criteria

Sounds good... I'll try it and let you know...

Thanks a lot.

Damian

Not applicable

Re: How to "left joining" using some criteria

Staffan... I thing I missed something ...

I have the MinMaxDate table... and now what?

Still can´t add the valid cost to my main table... what do I need to do now?

Regards.

Damian.

stabben23
Honored Contributor

Re: How to "left joining" using some criteria

Hi, put in the cost and and then a group by on Cost

MinMaxDate:

Load distinct
Item,

Cost

min(Date1) as MinDate,
max(Date2) as MaxDate
resident Your main table

group by Item, Cost

order by Item asc


stabben23
Honored Contributor

Re: How to "left joining" using some criteria

Ok, to fast answer from me, will have one more look, sorry.

stabben23
Honored Contributor

Re: How to "left joining" using some criteria

Hi, Put in Cost as I told you, now you have a mapping table. You can now use this table with applymap and "put" in your cost into your Invoice table. Now create a key with combination Item & Date, use this in a Mapping Load.

And then map the cost into your Invoice table with the same combination.

Temp:
LOAD
Item & Date as Key,
Cost
Resident MinMaxDate;

map:
Mapping Load
Key,
Cost
Resident Temp;
drop table Temp;

Invoice:
LOAD
Number,
ApplyMap('map', Article & CreationDate) as Cost,
CreationDate,
Article,
Quantity
FROM InvoiceTable


Not applicable

Re: How to "left joining" using some criteria

Hi Staffan. I'm not an expert on QV, so, I'm still confused. Let's see, I need:

1) MinMaxDate table grouping by item and cost.

2) I still need the calendar table? Because the field named "date" used for the key (table temp) is on calendar table...

3) then the map table

4) then I need to add the cost to the invoice table. You accomplish this using the applymap function?

Regards.

Damian.

Community Browser