Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date problem

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 =)

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Date problem

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

13 Replies
swuehl
Not applicable

Re: Date problem

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

Not applicable

Re: Date problem

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$);

swuehl
Not applicable

Re: Date problem

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

erich_shiino
Not applicable

Re: Date problem

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

Not applicable

Re: Date problem

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.

swuehl
Not applicable

Re: Date problem

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

Not applicable

Re: Date problem

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) ?

swuehl
Not applicable

Re: Date problem

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

Not applicable

Re: Date problem

Here is my qlikview example