From Excel formula to Qlick Script

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

Hi, I'm working in a script to determine the status of a item, I will explain you, I have in Excel two fields: one for initial date and other for final date, both for an activity, I have a Column called "ANS" and it calculates the difference between dates, and I have another column that is based in “ANS” and with a formula, it determines 3 possible values : "Cumple", "No Cumple", "Pendiente", additional, I have a column called "tipo de servicio", it determines if the service is "Fibra Oscura", and other services in Excel I have this formula:

=SI(Y(BJ55799="PENDIENTE");"PENDIENTE";SI(Y(BJ55799<=7;AP55799="VIABILIDAD");"Conforme";SI(Y(AP55799="PREVIABILIDAD";BJ55799<=2);"Conforme";SI(Y(L55799="FIBRA OSCURA";BJ55799<=8);"Conforme";"NC"))))

Where I compare the time in days for example: if the final date is "PENDIENTE", it shows "PENDIENTE", for the other if the process is "VIABILIDAD" and the duration time in days is less or equal to 7: "Conforme" else "NC", the same for "PREVIABILIDAD", but i have different time if additional to process type, the service type is "FIBRA OSCURA". and it goes perfect, it shows me the amount of "Cumple”, “No Cumple” etc etc... some like this:

in Qlik sense I have this expression:

If([Fecha Respuesta Diseño]='PENDIENTE',

'Pendiente',

If([Tipo Servicio]='FIBRA OSCURA' and [Proceso]='PREVIABILIDAD' and ([Fecha Respuesta Diseño]-[Fecha Creacion])<=8,

'Cumple',

If([Tipo Servicio]='FIBRA OSCURA'and [Proceso]='VIABILIDAD' and ([Fecha Respuesta Diseño]-[Fecha Creacion])-[Tiempo muerto]<=10,

'Cumple',

If([Proceso]='PREVIABILIDAD' and ([Fecha Respuesta Diseño]-[Fecha Creacion])<=2,

'Cumple',

If([Proceso]='VIABILIDAD' AND ([Fecha Respuesta Diseño]-[Fecha Creacion])-[Tiempo muerto]<=7,

'Cumple',

'No Cumple'))))) As [conformid_pru],

My idea is to avoid to create too many formulas in Excel, but my result is this:

I´m sure there are some values with “No Cumple” of the type "Fibra Oscura" that should be in "Cumple" but the grafic doesn’t show them.

Thanks a lot for your help!!!!!!.

topic From Excel formula to Qlick Script in QlikView

From Excel formula to Qlick Script