Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Re: How to create a new field from 2 exiting ones

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.

8 Replies
Not applicable

Re: How to create a new field from 2 exiting ones

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

MVP
MVP

Re: How to create a new field from 2 exiting ones

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

Re: How to create a new field from 2 exiting ones

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

Re: How to create a new field from 2 exiting ones

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
Valued Contributor

Re: How to create a new field from 2 exiting ones

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";

MVP
MVP

Re: How to create a new field from 2 exiting ones

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

Re: How to create a new field from 2 exiting ones

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

Re: How to create a new field from 2 exiting ones

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

Community Browser