Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
takama13
New Contributor II

Intervalmatch to correct values

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 CodeDateEventShould be
MAN01/06/2016Event1MAGE
MAN01/05/2018Event2MTS
COL01/06/2016Event3COLU
COL01/06/2018Event4CJS
VES01/01/2016Event5VESP
VES01/07/2018Event6VESP
MTS01/06/2016Event7MTS
MTS01/06/2018Event8CTPS

Table2 :  

Service CodeDateEventBShould be
MAN01/06/2016Event1MAGE
MAN01/05/2018Event2MTS
COL01/06/2016Event3COLU
COL01/06/2018Event4CJS
VES01/01/2016Event5VESP
VES01/07/2018Event6VESP
MTS01/06/2016Event7MTS
MTS01/06/2018Event8CTPS
VIC01/02/2017Event9VIC

Service Code correction:    

Service CodeFromToNew Service Code
MAN01/01/201501/04/2017MAGE
MAN02/04/201705/05/2018MTS
COL01/01/201504/04/2017COLU
COL05/04/201701/01/2019CJS
VES01/01/201501/01/2019VESP
MTS26/04/201801/01/2019CTPS

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:

     

DateEventBService CodeShould be 2New Service Code
01/01/2016Event5VESVESPVESP
01/06/2016Event1MANMAGEMAGE
01/06/2016Event3COLCOLUCOLU
01/05/2018Event2MANMTSMTS
01/06/2018Event4COLCJSCJS
01/06/2018Event8MTSCTPSCTPS
01/07/2018Event6VESVESPVESP

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch to correct values

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


talk is cheap, supply exceeds demand

View solution in original post

12 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch to correct values

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


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
takama13
New Contributor II

Re: Intervalmatch to correct values

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 ?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch to correct values

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.




talk is cheap, supply exceeds demand
Highlighted
takama13
New Contributor II

Re: Intervalmatch to correct values

That does not change the result.

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch to correct values

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.


talk is cheap, supply exceeds demand
Highlighted
takama13
New Contributor II

Re: Intervalmatch to correct values

ok, there is something weird.

Here is an example of what did not work :

   

Service CodeNew Service CodeDate
COLCJS16-07-2018 22:54:00
COLCJS16-07-2018 12:00:00
COL 15-07-2018 19:30:00
COLCJS15-07-2018 05:05:00
COLCJS

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch to correct values

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.


talk is cheap, supply exceeds demand
Highlighted
takama13
New Contributor II

Re: Intervalmatch to correct values

As a value (aligned to the right)

Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch to correct values

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.


talk is cheap, supply exceeds demand