Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create a new field from 2 exiting ones

Hello,

I have two fields. One is named "Register Date" that contains the date when an incident changes its status. The other one is called "Status" and tells me the status that the incident had from the date on the "Register Date". So, for each of the different status an incident can be, I have in a separate field the date when it changed to this status. I would like to crete a new field called "Date Opened" that would give me the date (from "Register Date") when the incident was set into "Status" opened, but I don't know how to do it.

To make it clearer, I have a table like this:

ID     Register Date     Status

5          10/03/2012     Opened

5          11/03/2012     Assigned

5          12/03/2012     Closed

How could I create a new filled called "Opened date" that would be 10/03/2012?

Many thanks in advance

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

I am not sure which database you are using, but use this script below

Data:

LOAD

     *,

     IF(ESTADO = 'Pendiente', [FECHA_REGISTRO]) AS [Fecha Pendiente];

SELECT CODIGO as "Código Incidencia",
ESTADO,
"FECHA_REGISTRO",
ID as "ID Incidencia"
FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

Regards,

Jagan.

View solution in original post

8 Replies
Not applicable
Author

IF(Status = 'Opened',[Register Date]) AS [Opened date]

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

LOAD

     ID,

     [Register Date],

     Status,

     IF(Status = 'Opened', [Register Date]) AS OpenedDate

FROM Datasource;

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

Hello,

Thanks for the answer. I Tried this:

 

SQL

SELECT CODIGO as "Código Incidencia",
ESTADO,
"FECHA_REGISTRO",
IF(ESTADO = 'Pendiente', [FECHA_REGISTRO]) AS Fecha Pendiente
ID as "ID Incidencia"
FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

But it is not working, what am I doing wrong?

Not applicable
Author

Hi jargan,

Thanks for the answer. I tried this:

 

SQL

SELECT CODIGO as "Código Incidencia",
ESTADO,
"FECHA_REGISTRO",
IF(ESTADO = 'Pendiente', [FECHA_REGISTRO]) AS Fecha Pendiente
ID as "ID Incidencia"
FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

But it is not working. What am I doing wrong? Thanks in adavance

fdelacal
Specialist
Specialist

Hola, prueba a hacer los calculos en la parte de qlikview y deja la sentencia sql tal cual.

espero qeu te ayude!..

LOAD

CODIGO as [Código Incidencia],

ESTADO,

FECHA_REGISTRO,

IF(ESTADO = 'Pendiente', FECHA_REGISTRO) AS Fecha Pendiente,

ID as [ID Incidencia]

SQL

SELECT CODIGO ,
ESTADO,
"FECHA_REGISTRO",
ID
FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

jagan
Luminary Alumni
Luminary Alumni

Hi,

I am not sure which database you are using, but use this script below

Data:

LOAD

     *,

     IF(ESTADO = 'Pendiente', [FECHA_REGISTRO]) AS [Fecha Pendiente];

SELECT CODIGO as "Código Incidencia",
ESTADO,
"FECHA_REGISTRO",
ID as "ID Incidencia"
FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

Regards,

Jagan.

Not applicable
Author

You have two options:

SQL Syntax:

SQL

SELECT

     ID,

     [Register Date],

     Status,

     CASE Status WHEN 'Opened' then [Register Date] END AS [Opened Date]

FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

AQL Syntax (QlikView):


LOAD

     ID,

     [Register Date],

     Status,

     IF (Status = 'Opened', [Register Date]) AS [Opened Date];

SQL SELECT

     ID, [Register Date], Status

FROM SERVICEONE."SOV_BI_INCIDENTHISTORY";

Regards,

Ricardo

Not applicable
Author

Thanks for all your answers. I tried this one and it worked! Thank you so much!