How to replace a set analysis string in load script editor

[s_gorgo]

Hello,

I need your help! I'd like to understand how to replace a set analysis string in load script editor.

The field I would like to add directly to the table "tab_MASTER_ORDINI" is the following:

Count({$<Data = {">=$(=[DataValidazioneVenerdì]) <=$(=[DataConsegnaRichiestaVenerdìMeno])"}, GgLav={"1"}>} Data) as GgLavValidazione

The field permit me to know the number of working days (custom calendar) betxween two dates.

• field Data --> table Calendario
• field DataValidazioneVenerdì , DataConsegnaRichiestaVenerdìMeno --> table tab_MASTER_ORDINI

Database:

tab_MASTER_ORDINI (.accdb file): 

LIB CONNECT TO 'K--DiPR-PPlan-_PPlan-DB_produzione.accdb';

LOAD Autore,
AutoreModifica,
Cliente,
CodiceCliente,
[...]
SettimanaMAD,
SettimanaMontaggio,
Stato,
StatoODP,
TipoOrdine,
TM,
UDM,
If (TipoOrdine = 'KD', 'pianificato', If(TipoOrdine = 'ZCAB', 'mobili', 'generici')) as StatoOrdine,
If (isnull(DataConsegnaRichiesta),'noDateReq',If (MAD <= DataConsegnaRichiesta, 'onTime', 'onDelay')) as Validazione,
Date(WeekEnd(DataValidazione)-2,'DD/MM/YYYY') as DataValidazioneVenerdì,
Date(WeekEnd(DataConsegnaRichiesta)-2,'DD/MM/YYYY') as DataConsegnaRichiestaVenerdì,
Date(WeekStart(DataConsegnaRichiesta)-3,'DD/MM/YYYY') as DataConsegnaRichiestaVenerdìMeno;
SQL SELECT Autore,
AutoreModifica,
Cliente,
CodiceCliente,
[...]
SettimanaMAD,
SettimanaMontaggio,
Stato,
StatoODP,
TipoOrdine,
TM,
UDM
FROM `tab_MASTER_ORDINI`;

How can I insert field "GgLavValidazione" directly in the table?

Thank you so much!!!! 😉

[Stoyan_Terziev]

Hello,

I think you are searching for the NetWorkDays() function.

Add the statement marked in GREEN to your scrip and let me know if it all worked out. 😉

LIB CONNECT TO 'K--DiPR-PPlan-_PPlan-DB_produzione.accdb';

LOAD Autore,
AutoreModifica,
Cliente,
CodiceCliente,
[...]
SettimanaMAD,
SettimanaMontaggio,
Stato,
StatoODP,
TipoOrdine,
TM,
UDM,
If (TipoOrdine = 'KD', 'pianificato', If(TipoOrdine = 'ZCAB', 'mobili', 'generici')) as StatoOrdine,
If (isnull(DataConsegnaRichiesta),'noDateReq',If (MAD <= DataConsegnaRichiesta, 'onTime', 'onDelay')) as Validazione,
Date(WeekEnd(DataValidazione)-2,'DD/MM/YYYY') as DataValidazioneVenerdì,
Date(WeekEnd(DataConsegnaRichiesta)-2,'DD/MM/YYYY') as DataConsegnaRichiestaVenerdì,
Date(WeekStart(DataConsegnaRichiesta)-3,'DD/MM/YYYY') as DataConsegnaRichiestaVenerdìMeno,
NetWorkDays(Date(WeekEnd(DataValidazione)-2,'DD/MM/YYYY'), Date(WeekStart(DataConsegnaRichiesta)-3,'DD/MM/YYYY')) as NetWorkingDaysBetweenDates
;
SQL SELECT Autore,
AutoreModifica,
Cliente,
CodiceCliente,
[...]
SettimanaMAD,
SettimanaMontaggio,
Stato,
StatoODP,
TipoOrdine,
TM,
UDM
FROM `tab_MASTER_ORDINI`;

I Hope this helps!

Kind regards,

S.T.

[s_gorgo]

Hello! and thank you! 🙂

I could think to use Networkdays function, but, if possible, I would prefer use my personal calendar (table "Calendario") with the detail, for every date, if the day, for my factory, is workin day or not (GgLav = 1 --> working day, GgLav = 0 --> not working day).

I could insert the dates in the syntax "holiday" of the function Networkdays , but the list is very long (summer holiday, Christmas, Easter, patron saint, business closing days, etc for each year) and I should manage an "extra" calendar.

[Stoyan_Terziev · Accepted solution]

Hi again,

In that case you'd like to do a bit of configuration in your calendar table and make sure it accumulates the WorkingDay flag as shown in the example below (It doesn't matter at what date it starts). Make sure the Calendar is Ordered BY the date field using a Resident load.

The accumulation you can do using the Previous() and RangeSum() functions.

Id put an if statement to check if it's a working day or not to achieve that continuous ID throughout the non-working days.

After you have your calendar appearing as the table above, you need to do a mapping load to get the Date and the Accumulated number of days.

Then for your field you do ApplyMap('Calendar', Date1) - ApplyMap('Calendar', Date2) as [Desired Result].

I hope that helps!

Let me know if you have questions on how exactly to achieve this with code.

Kind regards,

S.T.

[s_gorgo]

You are a genius!!

I already have the field that you called "Backend_accumulation" bacause I have to know the number of working days for each year.

I will try ASAP! 👍

[s_gorgo]

I have a question......... 😐

Date1 and Date2 are two fields of another table.

So.. the syntax should be something like this:

• Date1 --> calendario.data = tab_MASTER_ORDINI.DataValidazione
• Date2 --> calendario.data = tab_MASTER_ORDINI.DataRichiestaConsegna

For this reason in my first function I have used set analysis.

How could i solve?

[Stoyan_Terziev]

Hi,

Thank you for the kind words 🙂

Here is how I presume your code should look like:

MapDateAccumulationIndexes:

MAPPING LOAD DISTINCT

Date,

Backend_accumulation

From Calendar.qvd

;

LIB CONNECT TO 'K--DiPR-PPlan-_PPlan-DB_produzione.accdb';

LOAD Autore,
AutoreModifica,
Cliente,
CodiceCliente,
[...]
SettimanaMAD,
SettimanaMontaggio,
Stato,
StatoODP,
TipoOrdine,
TM,
UDM,
If (TipoOrdine = 'KD', 'pianificato', If(TipoOrdine = 'ZCAB', 'mobili', 'generici')) as StatoOrdine,
If (isnull(DataConsegnaRichiesta),'noDateReq',If (MAD <= DataConsegnaRichiesta, 'onTime', 'onDelay')) as Validazione,
Date(WeekEnd(DataValidazione)-2,'DD/MM/YYYY') as DataValidazioneVenerdì,
Date(WeekEnd(DataConsegnaRichiesta)-2,'DD/MM/YYYY') as DataConsegnaRichiestaVenerdì,
Date(WeekStart(DataConsegnaRichiesta)-3,'DD/MM/YYYY') as DataConsegnaRichiestaVenerdìMeno,
ApplyMap('MapDateAccumulationIndexes', Date(WeekEnd(DataValidazione)-2,'DD/MM/YYYY'), 'n/a')-
ApplyMap('MapDateAccumulationIndexes', Date(WeekStart(DataConsegnaRichiesta)-3,'DD/MM/YYYY'), 'n/a') as [NetWorkingDaysBetweenDates]

;
SQL SELECT Autore,
AutoreModifica,
Cliente,
CodiceCliente,
[...]
SettimanaMAD,
SettimanaMontaggio,
Stato,
StatoODP,
TipoOrdine,
TM,
UDM
FROM `tab_MASTER_ORDINI`;

Let me know if this helps!

Kind regards,

S.T.

[s_gorgo]

Mmmmmm... maybe i don't understand.

I entered this code:

Calendar:
MAPPING LOAD DISTINCT
Data,
NrGgLavProgressivo
FROM [lib://Calendario/tabCalendario.xlsx]
(ooxml, embedded labels, table is Calendario);

And this field in tab_MASTER_ORDINI:

ApplyMap('Calendar', Date(WeekEnd(DataValidazione)-2,'DD/MM/YYYY'), 'n/a')-ApplyMap('Calendar', Date(WeekStart(DataConsegnaRichiesta)-3,'DD/MM/YYYY'), 'n/a') as [GgLavValidazione];

But.. all record are empty:

[Stoyan_Terziev]

Hello,

I suppose the [Data] field in your Calendar table isn't formatted as Date with the same format as the other fields.

Let's get the exact script offline.

Regards,
S.T.

[s_gorgo]

Yes, you're right.

I've tried to use the function with other Date fields and it works perfectly. I suppose that the error is the format of the field DataValidazioneVenerdì. Looking in the table I found that the format isn't "Date", but "Date and Time" (see the clock icon).

I don't know very well Qlik data formats, but I don't understand because the field DataConsegnaVenerdìMeno is right while the others, created with the same function, are not. 😔