topic Load selected dates from qvd file. in QlikView

Load selected dates from qvd file.

jagannalla — Wed, 03 Aug 2011 09:44:07 GMT

Hi,

I've a Date field in format of timestamp. In one variable i'm storing different number of dates.

For eg:

vDates=07-01-2011,07-02-2011,07-05-2011.

- Now when i loading the qvd file i want to write where condition where the date is equal to all dates of variable.

For Eg;

load * from table.qvd

where match(Date,'$(vDate)');

- But the match function is not working for me. Can anyone help where i'm missing.

Re: Load selected dates from qvd file.

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 09:56:48 GMT

Hi,

Dates as such are interpreted as text, so each value needs to be single quoted in order to work in a match function. Take the following example

SET vDates = '01/01/2011','03/05/2011'; // Each date is comma separated, single quoted
Data:
LOAD * INLINE [
Date, ID
01/01/2011, A
03/05/2011, B
06/07/2011, C
]
WHERE Match(Date, $(vDates));

The above will only load IDs A and B.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Load selected dates from qvd file.

Wed, 03 Aug 2011 10:00:51 GMT

I think following code will help you. Instead of placing those dates in variable, place them in an inline table.

Table C will contains all the records from Table A except those which are found in Table B.

IE, Table C = Table A - Table B.

TableA:

LOAD * INLINE [

Customer, Code

A, 1000

B, 2000

C, 3000

D, 4000

E, 5000

F F,6000

];

TableB:

LOAD * INLINE [

Client, Number

B, 2000

D, 4000

G, 7000

];

TableC:

LOAD Customer AS Cust,

Code AS Cod

RESIDENT TableA WHERE NOT EXISTS(Client, Customer);

Regards

Load selected dates from qvd file.

jagannalla — Wed, 03 Aug 2011 10:16:55 GMT

Thanks miguel it is working in your code. But it is not working with my qvd file. I'll cross check every thing where i'm missing and let you now when it's works.

Re: Load selected dates from qvd file.

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 10:31:08 GMT

Hi,

Note that in the Match() function I'm not quoting the $(vDates) variable, since it's already quoted the values within.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Load selected dates from qvd file.

Wed, 03 Aug 2011 10:59:53 GMT

Hi Miguel,

I am also facing the same problem

I am not able to load the data with where condition by passing variable.

please check the attached files of myne and let me know how they work.

Thanks....

Re: Load selected dates from qvd file.

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 11:15:49 GMT

Hi,

First, please, since it's a different question, create a new post with it.

Second, there are two things here, but it all comes to formatting. On your first document, the variable vDates needs to return comma separated, single quoted dates to the variable. So replace in the Variable Overview your vDates variable from using GetFieldSelections to

=Chr(39) & Concat(DISTINCT CaptureDate, Chr(39) & Chr(44) & Chr(39)) & Chr(39)

Now you have the dates as to be used in the Match function.

In the Output document, use the Macth() function as shown above

Where Match(Date(Floor(CaptureDateTime)), $(vDates));

Floor() function is needed because the actual field has not only dates but times as well.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Load selected dates from qvd file.

jagannalla — Wed, 03 Aug 2011 11:38:04 GMT

Miguel,

I'm facing with qvd file . I'm attaching my qvw file and qvd can you check once plz...

Re: Load selected dates from qvd file.

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 11:56:07 GMT

It's funny how all of you are having the same exact issues with the same exact field names.

Anyway, do as I mentioned above to get the formatting properly using Floor() to get only the date part (not the time part)

Match(Date(Floor(CaptureDateTime)), $(vTotalDates))

And make sure the variable stores the same format of date (for example "MM-DD-YYYY").

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Nota Bene: Please read the answers given to other users on the same subject prior to ask the same twice. Hence all of us will save time and keep the threads tidier.