Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
beck_bakytbek
Honored Contributor

two date-column into one date column

Hi Folks,

i got a question, i have 2 columns;

StartDate,    EndDate

02,02.2015    02.12.2017

03.12.2017    03.03.2018

i want to know, is that possible to combine 2 columns into one, if it is possible, how can i concatenate the combined column with my maser calender?

does anybody have any idea?

Beck

11 Replies
Digvijay_Singh
Honored Contributor III

Re: two date-column into one date column

Not sure the exact need but you may think of like this:

Temp:

Load StartDate as Date

Resident table1;

Concatenante(Temp)

Load EndDate as Date

Resident table1;

Now you can find Max and Min Date and create master calendar out of your Date column.

beck_bakytbek
Honored Contributor

Re: two date-column into one date column

Hi Sinh,

thanks a lot for your responce and help,

i have a question, as you see i have two columns and i want to compare two columns, for instance:

if (02.02.2015 < 02.12.2017, 'non active', 'active') as State,

can i create such field: State and later combine two columns into one.

ychaitanya
Contributor III

Re: two date-column into one date column

Hello

Extract_Table:

Load StartDate,

EndDate,

Some_Primary_Key

Resident Table1;

Join

Load CalendarDate as Temp_Date

Resident Your_Master_Calendar_Table;

// Single Column to have extract of Start and End Dates

NoConcatenate

Final _Table:

Load Some_primary_Key,

Temp_Date as Single_Date

Resident Extract_Table

where Temp_Date>=StartDate and TempDate<=EndDate;

Now if you need to find the Min Date and Max Date for the Primary Keys. -- . Just use the Min(Single_Date) and Max(Single_Date).

you could also use count(distinct Single_Date) to find the days between.

We have got the same problem and i have did this and got the solution .

Please let me know if this works.

Thanks

CY

beck_bakytbek
Honored Contributor

Re: two date-column into one date column

Hi Yelisetty,

first of all thanks a lot for your responce and time:

my issue is: 1) i want to create the Field: State, the Idea is: to those columns belong the projects, i want to show, that the project until certain date is active and non active, therefore i want to create the Field: State

2)  then i want to create the Filter: State and can select and show on the time line my active and non active project.

is that clear explained?

Thanks a lot

Digvijay_Singh
Honored Contributor III

Re: two date-column into one date column

You can do that, for each record comparison will be made, ensure they are formatted as date.

beck_bakytbek
Honored Contributor

Re: two date-column into one date column

Do you have an example to this issue?

zebhashmi
Valued Contributor

Re: two date-column into one date column

Looks fine to me just change the dates into field names

if (field1 < field2, 'non active', 'active') as State,

beck_bakytbek
Honored Contributor

Re: two date-column into one date column

Hi Hashmi,

can you show it on base of above example / issue

zebhashmi
Valued Contributor

Re: two date-column into one date column

to me, if that's what you are looking

QCtemp:

load * inline [


StartDate,    EndDate


02/02/2015,    02/12/2017


03/12/2017,    03/03/2018

];

QC:

load *,

if (StartDate < EndDate, 'non active', 'active') as State


Resident QCtemp;


Drop table QCtemp;