topic Re: Create a new field joining two dates in QlikView

Create a new field joining two dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello

I have the following case:

I need a new field, for example: FECHA PDN in order to create a table wich must have the sum of the FECHA DIGITACIÓN and FECHA AUDITORIA as a result. In this case, if we want to know the result of 22/12/2016 would be 5.700 (1.085+ 4.615).

Thank you very much for your comments and time.

P:D I have a personal edition license, so I can not open any .qvw you could send to me.

Re: Create a new field joining two dates

sunny_talwar — Fri, 23 Dec 2016 18:57:58 GMT

This is what you need

Canonical Date

Re: Create a new field joining two dates

Anonymous — Fri, 23 Dec 2016 19:21:39 GMT

Sunny, thank you so much.

I will study that document you suggest. It seems to be a little hard to understand to me.

Best regards

Re: Create a new field joining two dates

sunny_talwar — Fri, 23 Dec 2016 19:22:59 GMT

I guess without a sample it was always going to be difficult

Re: Create a new field joining two dates

vinieme12 — Fri, 23 Dec 2016 19:47:33 GMT

Are your date fields , fecha digitacion and fecha auditorium coming from different tables?

Are these tables associated?

Please post sample data that replicates your scenario

Re: Create a new field joining two dates

Anonymous — Mon, 26 Dec 2016 20:52:03 GMT

Thank you Vineeth

Both of them are from the same table.

I am using the following expressions:

To FECHA AUDITORIA:

count({<[ESTADO GENERAL]={'COMPLETADA'}, [PERIODO ACTUAL?]={'SI'}>} REGIONAL2)

To FECHA PROCESADAS:

count({< [ESTADO GENERAL]={'NO COMPLETADA'}, FASE={'INGRESADA A SW'}, [PERIODO ACTUAL?]={'SI'}>} REGIONAL2)

Attached a piece of the BD I am using

Thank you

Re: Create a new field joining two dates

vinieme12 — Mon, 02 Jan 2017 15:33:52 GMT

using your sample

Dimension = DateIS

Expression = sum({<DateType = {'AUDIT','DIGI'}>}Processed)

/////////////////////////////// SCRIPT //////////////////////////////////////////////////////////////////////////////

FACT:
LOAD NUMERORADICADO,
REGIONAL,
[FECHA RADICACION],
[ESTADO CM],
[FECHA AUDITORIA],
[FECHA INGRESO RADICACION],
[FECHA DIGITACION],
FASE,
[ESTADO GENERAL],
[PERIODO ACTUAL?],
LOTE,
CUMPLIMIENTO,
[PRESENTA PRECINTO?],
rand()*10 as Processed,
rand()*9 as completed
FROM

(ooxml, embedded labels, table is [MAESTRA FACTURAS]);

MasterCalendar:
LOAD NUMERORADICADO,
[FECHA AUDITORIA] as DateIS,
'AUDIT' as DateType,
Year([FECHA AUDITORIA]) as YearIS,
Month([FECHA AUDITORIA]) as MonthIS
Resident FACT;

Concatenate
LOAD NUMERORADICADO,
[FECHA DIGITACION] as DateIS,
'DIGI' as DateType,
Year([FECHA DIGITACION]) as YearIS,
Month([FECHA DIGITACION]) as MonthIS
Resident FACT;

///////////////////////////////End of SCRIPT //////////////////////////////////////////////////////////////////////////////

Read these posts

Search Recipes | Qlikview Cookbook

Tutorial - Using Common Date Dimensions and Shared Calendars

Re: Create a new field joining two dates

Anonymous — Tue, 03 Jan 2017 18:47:38 GMT

Vineeth Pujari, thanks a lot.

It works perfectly.

very thankful

Re: Create a new field joining two dates

vinieme12 — Tue, 03 Jan 2017 19:07:33 GMT

Glad to help

Can you close this thread please , by marking a response as correct

Qlik Community Tip: Marking Replies as Correct or Helpful