topic Re: How to "left joining" using some criteria in QlikView

How to "left joining" using some criteria

Mon, 09 Dec 2013 18:56:31 GMT

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.

Re: How to "left joining" using some criteria

Anonymous — Mon, 09 Dec 2013 19:07:13 GMT

Damian

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

This Blog Post IntervalMatch by hicexplains it well.

Best Regards, Bill

Re: How to "left joining" using some criteria

Mon, 09 Dec 2013 19:15:59 GMT

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.

Re: How to "left joining" using some criteria

stabben23 — Mon, 09 Dec 2013 19:22:06 GMT

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;

Re: How to "left joining" using some criteria

Mon, 09 Dec 2013 19:44:42 GMT

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

Thanks a lot.

Damian

Re: How to "left joining" using some criteria

Mon, 09 Dec 2013 19:57:30 GMT

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.

Re: How to "left joining" using some criteria

stabben23 — Mon, 09 Dec 2013 20:03:30 GMT

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

Re: How to "left joining" using some criteria

stabben23 — Mon, 09 Dec 2013 20:08:15 GMT

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

Re: How to "left joining" using some criteria

stabben23 — Tue, 10 Dec 2013 08:39:40 GMT

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

Re: How to "left joining" using some criteria

Tue, 10 Dec 2013 16:33:45 GMT

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.

Re: How to "left joining" using some criteria

stabben23 — Tue, 10 Dec 2013 16:50:54 GMT

Hi Damian,

1.yes

2. yes, to get all dates between the from and to dates.

3. yes, you will now have a table with all dates, grouped by item and cost.

You will have a match on all CreationDate, whatever the date is, into your map table.

4. Applymap in the most common and effective way to merge singlefields into a bigger table.

Let give it a try, i will help if your lost.

//Staffan

Re: How to "left joining" using some criteria

Tue, 10 Dec 2013 17:54:45 GMT

Hi Staffan, I'm trying...

This lines:

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

Will take a MinDate from record number 0 and MaxDate from record number 0 also. Grouping by item and cost, the record number 0

may not have the actual mininum date and maximum date... I'm right? Because some items may have date ranges that others items may not have.

May be I need first

MinMaxDate:

Load

min(Date1) as MinDate,
max(Date2) as MaxDate
resident CostHistory

then

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

Is that right?

And what about the INTERVALMATCH? I still need it?

Regards.

Damian.

Re: How to "left joining" using some criteria

stabben23 — Tue, 10 Dec 2013 18:25:21 GMT

Hi, Try the hole script, there are maybe 10 way to solv this problem, this is one of them.

And yes IntervallMatch is needed here.

cost:

LOAD

Article as Item,
FromDate as FromDate,
ToDate as ToDate,
Cost
FROM
CostTable;

MinMaxDate:
Load
Item,
Cost,
min(FromDate) as MinDate,
max(ToDate) as MaxDate
resident cost
group by Item, Cost
order by Item asc;

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

Calendar:
Load recno()+$(vMinDate)-1 as Date
Autogenerate vMaxDate - vMinDate;
JOIN (MinMaxDate) INTERVALMATCH (Date) LOAD MinDate, MaxDate
RESIDENT MinMaxDate;

drop fields MinDate,MaxDate;
NoConcatenate
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
Drop Tables Calendar, cost, MinMaxDate ;

Re: How to "left joining" using some criteria

Tue, 10 Dec 2013 20:10:24 GMT

Thank you Staffan.

Trying the script. Have a problema with the sentence

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

When the record count for MinMaxDate arrives to 6869841

gives me the error: OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating 2 MB

Seems like I need a more powerfull PC...

Re: How to "left joining" using some criteria

stabben23 — Tue, 10 Dec 2013 20:43:20 GMT

Hi Damian,

Maybe there is some error in your FromDate ToDate, feels like you havent got a ToDate for some item.

Can you send that file?

Re: How to "left joining" using some criteria

Wed, 11 Dec 2013 01:08:43 GMT

Yes, here it is, compressed.

You must look at the table ArticulosFabricaCostoHist for the cost history for each article.

And Comprobantes is the table of invoices (headers and details).

Normally, the ToDate is null when the cost is valid. For example:

Article FromDate ToDate Cost

A1 01/01/2013 31/01/2013 20

A1 01/02/2013 30/06/2013 22

A1 01/07/2013 <null> 25

Means that 25 is the valid cost for article A1 today.

But in the script I wrote the following to avoid this case:

(IsNull(ToDate), MakeDate(Year(Today()), 12, 31), ToDate)

Replacing the NULL by the last day of the current year.

Ops, can't find a way to add an attachment...

Thanks a lot.

Regards

Damian.

Re: How to "left joining" using some criteria

stabben23 — Wed, 11 Dec 2013 06:42:04 GMT

Hi,

The first thing, will this (IsNull(ToDate), MakeDate(Year(Today()), 12, 31), ToDate) gives you 31/12/2013? Otherwise you will have problem to catch ToDate on that row. Make sure that you have exact the same format in your date fields.

//Staffan

Re: How to "left joining" using some criteria

Wed, 11 Dec 2013 15:01:30 GMT

You right. I left the LOAD as before, without the IsNull. It's fine now.

But, I need something like that, because the MinMaxDate table doesn't all the days, it's stop when a NULL date is found. For the previous example:

Article FromDate ToDate Cost

A1 01/01/2013 31/01/2013 20

A1 01/02/2013 30/06/2013 22

A1 01/07/2013 <null> 25

I get the following MinMaxDate table:

Article Date MinDate MaxDate Cost

A1 01/01/2013 01/01/2013 31/01/2013 20

02/01/2013

01/01/2013

31/01/2013

and so on until 31/01/2013

A1 01/02/2013 01/02/2013 30/06/2013 22

02/02/2013

01/02/2013

30/06/2013

and so on until 30/06/2013

A1 <null> 01/07/2013 <null> 25

but nothing else, no more days

Regards.

Damian.

Re: How to "left joining" using some criteria

stabben23 — Wed, 11 Dec 2013 17:49:27 GMT

Hi, you can use this to get a ToDate if there is a null value. today +1 will give you 13/12/2013

if(isnull(ToDate),today()+1,ToDate) as ToDate

Otherwise it looks ok., keep up the good work.

//Staffan

Re: How to "left joining" using some criteria

Wed, 11 Dec 2013 20:41:17 GMT

Thanks Staffan.

This way also gives me the error

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating 2 MB

I also try this way

if(isnull(ToDate),Date(today()+1),ToDate) as ToDate

but I get the error also...

I get the date from an ODBC connection. Can you recomend something to take in mind refering to date format?

Regards

Damian.