Intervalmatch to correct values

[takama13]

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

[Gysbert_Wassenaar · Accepted solution]

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

[takama13]

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 ?

[Gysbert_Wassenaar]

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.

[takama13]

That does not change the result.

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

[Gysbert_Wassenaar]

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.

[takama13]

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

[Gysbert_Wassenaar]

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.

[takama13]

As a value (aligned to the right)

[Gysbert_Wassenaar]

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.