topic Re: How to replace a set analysis string in load script editor in App Development

How to replace a set analysis string in load script editor

s_gorgo — Sat, 16 Nov 2024 04:15:33 GMT

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!!!! 😉

Re: How to replace a set analysis string in load script editor

Stoyan_Terziev — Mon, 28 Oct 2019 13:06:26 GMT

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.

Re: How to replace a set analysis string in load script editor

s_gorgo — Mon, 28 Oct 2019 13:19:11 GMT

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.

Re: How to replace a set analysis string in load script editor

Stoyan_Terziev — Mon, 28 Oct 2019 13:39:41 GMT

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.

Re: How to replace a set analysis string in load script editor

s_gorgo — Mon, 28 Oct 2019 16:17:47 GMT

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! 👍

Re: How to replace a set analysis string in load script editor

s_gorgo — Mon, 28 Oct 2019 16:24:01 GMT

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?

Re: How to replace a set analysis string in load script editor

Stoyan_Terziev — Mon, 28 Oct 2019 16:40:57 GMT

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.

Re: How to replace a set analysis string in load script editor

s_gorgo — Mon, 28 Oct 2019 17:25:42 GMT

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:

Re: How to replace a set analysis string in load script editor

Stoyan_Terziev — Mon, 28 Oct 2019 18:20:35 GMT

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.

Re: How to replace a set analysis string in load script editor

s_gorgo — Tue, 29 Oct 2019 10:46:25 GMT

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. 😔

Re: How to replace a set analysis string in load script editor

Stoyan_Terziev — Tue, 29 Oct 2019 10:49:43 GMT

Hi,

I'm glad you are satisfied with the resolution!

Not sure that's going to work as is but you can do Date(Floor([DataField])) to make it a 'regular' Date.

That's a tricky topic in QV, you can check the Date() and Date#() functions.

Kind regards,

S.T.

Re: How to replace a set analysis string in load script editor

s_gorgo — Tue, 29 Oct 2019 12:11:48 GMT

I don't know why, but:

Weekend function return "Date and Time" data format;
Weekstart function return "Date" data format.

At least in my app 😅