topic Re: Date problem in QlikView

Date problem

Mon, 04 Jul 2011 15:02:23 GMT

Hello everyone !

I have two types of date : an enter date and an exit date ( DD-MM-YYYY ). I'd like to merge this two cells and have this display MM-YYYY.

How can I do ??

Thank you 😃

Re: Date problem

swuehl — Mon, 04 Jul 2011 16:19:15 GMT

Hi Annie,

second part of your question is easy: use date function to format the output:

date ( expression [,format-code])

e.g. in any expression

=date( Date, 'MM-YYYY')

{"Date" is a field with date format, you may need to parse your dates into correct format first, depending on your system settings}

To receive your merged list, where are the two dates located, in separate tables?

I would go for adding the merge to the load script. Could you post an excerpt of your current script?

Regards,

Stefan

Re: Date problem

Tue, 05 Jul 2011 08:04:23 GMT

Here are my two tables :

LOAD @1,

@2,

@3,

@4,

@5 as [Date_enter]

FROM

[...]

(biff, no labels, table is In$);

LOAD @1,

@2,

@3,

@4,

@5 as [Date_exit]

FROM

[...]

(biff, no labels, table is Out$);

Re: Date problem

swuehl — Tue, 05 Jul 2011 10:10:23 GMT

Hi Annie,

it always depends on what you want to achieve wit your date.

If you just want to merge the two dates into one field, and you don't need the other fields (@1 to @4),

I think you can concatenate the two tables:

Date:

LOAD

@5 as Date

FROM

[...]

(biff, no labels, table is In$);

CONCATENATE (Date) LOAD

@5 as Date

FROM

[...]

(biff, no labels, table is Out$);

{If number of fields and fieldnames are identical, in fact you don't need the CONCATENATE, but I think this makes it more clear}

Then use above expression to format the output.

But I guess you want to preserve the original enter and exit date data.

Then you can use a separate table and reuse the already read data:

EnterDate:

LOAD @1,

@2,

@3,

@4,

@5 as [Date_enter]

FROM

[...]

(biff, no labels, table is In$);

ExitDate:

LOAD @1,

@2,

@3,

@4,

@5 as [Date_exit]

FROM

[...]

(biff, no labels, table is Out$);

Date:

Load

//[Date_enter],

[Date_enter] as Date

resident EnterDate;

Concatenate (Date) Load

//[Date_exit],

[Date_exit] as Date

resident ExitDate;

If you remove the comments from the lines, your Date data will be associated with your enter resp. exit dates (so if you select a date, the corressponding enter resp exit dates are also selected). If you leave the comments in, you get an not associated table (data island).

Hope this helps,

Stefan

Re: Date problem

erichshiino — Tue, 05 Jul 2011 10:14:03 GMT

If the @1, @2, etc mean the same thing in both tables it can be like this:

( It will rename both fields to the same name and format them. You will need to use a flag to know if it is 'Enter' or 'Exit' event)

Table:

LOAD @1, 'Enter' as Fact,

@2,

@3,

@4,

date(@5,'MM/YYYY') as [Date]

FROM

[...]

(biff, no labels, table is In$);

CONCATENATE(Table) //Since all the fields are the same... the concanate is not actually necessary here

LOAD @1, 'Exit' as Fact

@2,

@3,

@4,

date(@5,'MM/YYYY') as [Date]

FROM

[...]

(biff, no labels, table is Out$);

Hope this helps,

Erich

Re: Date problem

Tue, 05 Jul 2011 12:35:49 GMT

Everything you write helps me. But I have some people who enter at the date_enter and people who leave at date_exit. And I want to make 2 graph ( enter & exit ). When I choose a date (mm-yyyy), it shows me on the both graph the enters and exits.

Re: Date problem

swuehl — Tue, 05 Jul 2011 12:41:21 GMT

Hi Annie,

that should not be a problem.

Either follow my last suggestion (uncomment lines), use a list box for date with my expression and a graph with =count(enter_date)

and

=count (exit_date)

as expressions.

Or use Erichs suggestions with a listbox for date and

Fact as dimension and count(Date) as expression.

I think this should be enough.

Regards,

Stefan

Re: Date problem

Tue, 05 Jul 2011 12:56:20 GMT

Thank you ! It works. But how can I show just unique value instead of having several times the same date and in the graph only one point for the month (mm-yyyy) instead of several points for every days in the month (dd-mm-yyyy) ?

Re: Date problem

swuehl — Tue, 05 Jul 2011 13:14:05 GMT

Hi Annie,

well, you should only have two bars in your graph in both cases if you followed my description (overall counts)...

If you want the counts per month in your graph, you could add a calculated dimension in my case:

=date(Date, 'MM-YYY')

or in Erichs case add Date as dimension, fact as second dimension.

If you post your example file here (there is an option in advanced editor to add files), I think we can work it out pretty fast.

Cheers,

Stefan

Re: Date problem

Tue, 05 Jul 2011 13:38:57 GMT

Here is my qlikview example

Re: Date problem

swuehl — Tue, 05 Jul 2011 13:57:25 GMT

Hi Annie,

the problem is that QlikView added a synthetic key because Name and Lastname are identical in tables Exit and Enter (you can see this in Table View CTRL-T).

If you have a data scheme like this, I think Erichs approach is more practicable since it does not only merge the date field, but also the Names and Lastname and add a flag for the event.

In the graph, either use two dimesions (first Date, then Fact) or if you want two graphs,

use

=sum(if (Fact='Enter',1))

resp.

=sum(if (Fact='Exit',1))

as expression (and only Date as dimension).

I don't have the xls Files, so I couldn't adapt your sample and try, but I think this should work.

Regards,

Stefan

Re: Date problem

Tue, 05 Jul 2011 14:15:43 GMT

I'm really really sorry.. it doesn't work... I add my excel file. Thank you very very much for your patience and your help !

Re: Date problem

swuehl — Tue, 05 Jul 2011 14:45:21 GMT

Hi Annie,

I worked on your example, please have a look.

I used Erichs approach for the load script, but used an additional monthstart for the dates to limit all events in a month to the same date.

(That's why you had more than one list entries before). If you need the detail date data, Remove this monthstart from the script and add it to the list box / graph calculated dimensions).

I added also the expressions for the graphs to show the appropriate events only.

Regards,

Stefan

Re: Date problem

Tue, 05 Jul 2011 15:01:03 GMT

Thank you so much Stefan ! That's exactly what I want !