topic Re: How do I get minimum and maximum values from two datasources in QlikView

How do I get minimum and maximum values from two datasources

Anonymous — Tue, 17 Apr 2012 07:57:06 GMT

I got two different datasource, on xslx and one qvd. I combine them with concatenate, that is not my problem. All data is shown in the qvw.

The Fields of the xslx and the qvd I want to use are the same. Same format and same valuetype. On of this value is the date the data was created.

Now my Problem is, that I want to use a Calendar where I can use the creation-date from both datasources. But I only get the dates from the qvd.

I hope an example will help to understand.

How do I get minimum and maximum values from two datasources

Tue, 17 Apr 2012 09:11:44 GMT

I can't view your example currently but I'd guess the dates are coming in as text strings from the xlsx and so min and max won't consider them as they aren't numerical (which true dates are). Try using the date#() function when loading in the xlsx to convert the string in to a true date.

Matt

Re: How do I get minimum and maximum values from two datasources

Anonymous — Tue, 17 Apr 2012 10:27:58 GMT

Thank you for your response.

I did what you suggested, but the result is the same.

Date(Date#(Datum,'DD.MM.YYYY'), 'DD.MM.YYYY') as Erfassungsdatum

The calendar has values from 02/2012 up to today.

If I select some date I can choose also dates from the xslx in the statusbox.

if I run the Skript, I can see that only 68 Datefields where added, that is the correct amount from the qvd.

Any new suggestions?

Thanks!

Re: How do I get minimum and maximum values from two datasources

Tue, 17 Apr 2012 11:05:08 GMT

I've had a quick look at your example. As the table loads look to be mocked up and because my German isn't great I can't be sure but the following where clause caught my eye:

where len(Datum)>0;

Are you sure the Datum field is in both the QVD and the XLSX? You are getting the date from a different field Erfassungsdatum instead so I'm not sure of its purpose.

Matt

Re: How do I get minimum and maximum values from two datasources

Anonymous — Tue, 17 Apr 2012 11:28:59 GMT

Thank´s for your reply.

The example I posted was from the qvw, which is seen at the accesspoint. Originally I only used the qvd with the data from 02/2012.

I got the xlsx file with the older data last week and with the then I had to use the field "Erfassungsdatum".

Sorry for this mistake.

The Calendar of my test qvw is build like this:

Min:

load

min(Erfassungsdatum) as Datumklein

Resident Geräusche where len(Erfassungsdatum)>0;

Max:

load

max(Erfassungsdatum) as Datumgross

resident Geräusche;

The field "Erfassungsdatum" (date of acquisition) exists in both

Re: How do I get minimum and maximum values from two datasources

Anonymous — Wed, 18 Apr 2012 07:57:28 GMT

Ok, I found the error.

The problem was that I wanted to concatenate the excel file like this:

concatenate(Geräusche)

ODBC CONNECT32 TO ...

LOAD Date...

After I put the concatenate directly infront of the load, everything went fine

ODBC CONNECT32 TO ...

concatenate(Geräusche)

LOAD Date...

Thanks Matt for your effort.