topic Re: Intervalmatch to correct values in QlikView

Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 08:09:43 GMT

Hi all,

My database is a set of 2 Excel files.

Some fields are the same (and used to link the 2 tables), some fields are different.

In each table, I have a field with a date of events.

But some data (Service Code) are wrong and I would like to correct them based on an Excel file I created with range dates.

Here below are my tables :

Table1 :

Service Code	Date	Event	Should be
MAN	01/06/2016	Event1	MAGE
MAN	01/05/2018	Event2	MTS
COL	01/06/2016	Event3	COLU
COL	01/06/2018	Event4	CJS
VES	01/01/2016	Event5	VESP
VES	01/07/2018	Event6	VESP
MTS	01/06/2016	Event7	MTS
MTS	01/06/2018	Event8	CTPS

Table2 :

Service Code	Date	EventB	Should be
MAN	01/06/2016	Event1	MAGE
MAN	01/05/2018	Event2	MTS
COL	01/06/2016	Event3	COLU
COL	01/06/2018	Event4	CJS
VES	01/01/2016	Event5	VESP
VES	01/07/2018	Event6	VESP
MTS	01/06/2016	Event7	MTS
MTS	01/06/2018	Event8	CTPS
VIC	01/02/2017	Event9	VIC

Service Code correction:

Service Code	From	To	New Service Code
MAN	01/01/2015	01/04/2017	MAGE
MAN	02/04/2017	05/05/2018	MTS
COL	01/01/2015	04/04/2017	COLU
COL	05/04/2017	01/01/2019	CJS
VES	01/01/2015	01/01/2019	VESP
MTS	26/04/2018	01/01/2019	CTPS

Here is the code I use to try and correct the tables :

Table1:

LOAD

[Service Code],

Date,

Event,

[Should be]

FROM

(ooxml, embedded labels, table is Feuil1);

Table2:

LOAD

[Service Code],

Date,

EventB,

[Should be] as [Should be 2]

FROM

(ooxml, embedded labels, table is Feuil1);

'Service Code correction':

LOAD

[Service Code],

From,

To,

[New Service Code]

FROM

(ooxml, embedded labels, table is Feuil1);

Inner Join ('Table1')

IntervalMatch ([Date],[Service Code])

LOAD [From], [To], [Service Code] Resident 'Service Code correction';

Right Join('Table1')

LOAD * Resident 'Service Code correction';

Inner Join ('Table2')

IntervalMatch ([Date],[Service Code])

LOAD [From], [To], [Service Code] Resident 'Service Code correction';

Left Join('Table2')

LOAD * Resident 'Service Code correction';

DROP Table 'Service Code correction';

DROP Field [From];

DROP Field [To];

The result is correct unless the fact that the lines with Service Code not present in 'Service Code correction' are removed.

I would like to keep them.

What am I doing wrong ?

Result for Table2:

Date	EventB	Service Code	Should be 2	New Service Code
01/01/2016	Event5	VES	VESP	VESP
01/06/2016	Event1	MAN	MAGE	MAGE
01/06/2016	Event3	COL	COLU	COLU
01/05/2018	Event2	MAN	MTS	MTS
01/06/2018	Event4	COL	CJS	CJS
01/06/2018	Event8	MTS	CTPS	CTPS
01/07/2018	Event6	VES	VESP	VESP

We can notice that the line with Service Code "VIC" disappeared. I would like to keep this line with 'New Service Code' column empty.

Thanks

Re: Intervalmatch to correct values

Gysbert_Wassenaar — Tue, 14 Aug 2018 08:17:55 GMT

Inner Join ('Table2')

IntervalMatch ([Date],[Service Code])

LOAD [From], [To], [Service Code] Resident 'Service Code correction';

Left Join('Table2')

LOAD * Resident 'Service Code correction';

Change the Inner Join to a Left Join if you want to keep all the records from Table2

Re: Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 09:45:25 GMT

Thank you, seems that is the solution.

But I now have an other issue : using this script with my full database (800,000 lines) seems to not work for some values.

If I reduce my database, more values are corrected.

I fear that the intervalmatch function creates a too large table which reach a limit (which one ?).

Would there be an alternative way to correct my values ?

Re: Intervalmatch to correct values

Gysbert_Wassenaar — Tue, 14 Aug 2018 09:55:10 GMT

Try changing the To dates in your interval table to DayEnd(To) as To so they cover the entire day. In other words 01/04/2017 which is now 01/04/2017 00:00:00 becomes 01/04/2017 23:59:59.

Re: Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 10:00:36 GMT

That does not change the result.

The values that are not changed are not necessary at the bounds.

Re: Intervalmatch to correct values

Gysbert_Wassenaar — Tue, 14 Aug 2018 10:08:58 GMT

Try to isolate a case that is giving an incorrect result. Then check that the data values are really really date values and not some sort of string value that merely looks like a date value.

Re: Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 12:49:28 GMT

ok, there is something weird.

Here is an example of what did not work :

Service Code	New Service Code	Date
COL	CJS	16-07-2018 22:54:00
COL	CJS	16-07-2018 12:00:00
COL		15-07-2018 19:30:00
COL	CJS	15-07-2018 05:05:00
COL	CJS	14-07-2018 06:36:00

I went to my database and changed the date to the 15/07/2018 19:31.

With this changed, it worked (so I though of about a bad format, as you said).

Then I changed it back to the 15/07/2018 19:30, and it's not working again...

Re: Intervalmatch to correct values

Gysbert_Wassenaar — Tue, 14 Aug 2018 12:59:21 GMT

What does that value turn into in Qlikview? In a listbox does it align left or right? If left then that would indicate it's interpreted as a string value by Qlikview.

Re: Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 13:02:46 GMT

As a value (aligned to the right)

Re: Intervalmatch to correct values

Gysbert_Wassenaar — Tue, 14 Aug 2018 13:31:50 GMT

Can you create a test case, i.e. a small qlikview document + source excel files, for this? Just enough data to have some records that work and one or two that don't.

Re: Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 13:50:21 GMT

Here are the files.

Re: Intervalmatch to correct values

Gysbert_Wassenaar — Tue, 14 Aug 2018 14:54:51 GMT

Looks like a bug. No idea what's happening. As soon as I change the Date even a tiny bit like rounding it to a fraction of a second with round(Date,1/86400000) all is well.

Re: Intervalmatch to correct values

takama13 — Tue, 14 Aug 2018 15:34:06 GMT

I will use this workaround until someone could explain this bug.

Thanks for your time.