Date problem

[#1]

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 😃

[swuehl]

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

[#3]

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]

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

[erichshiino]

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

[#6]

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]

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

[#8]

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]

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

[#10]

Here is my qlikview example