Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
IF(Status = 'Opened',[Register Date]) AS [Opened date]
Hi,
Try like this
LOAD
ID,
[Register Date],
Status,
IF(Status = 'Opened', [Register Date]) AS OpenedDate
FROM Datasource;
Hope this helps you.
Regards,
Jagan.
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?
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
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";
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.
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
Thanks for all your answers. I tried this one and it worked! Thank you so much!