Issue Migrating IntervalMatch from QlikView to Qlik Sense – Only Field Names, No Data Returned

[MarioGabriel]

Hello,

I'm currently in the process of migrating an existing QlikView application to Qlik Sense. One part of the script involves using IntervalMatch() to map account numbers to predefined level 3 ranges for account grouping.

However, after the migration, I’m facing an issue where Qlik Sense only returns the field names, but no actual data is shown in the resulting table (even though the script executes without error).

What I’ve done:

• Recreated the exact logic from QlikView using the same QVD files.

• Verified the data is present by previewing the individual source tables (accounts, intervals).

• Tried both INNER JOIN and LEFT JOIN around IntervalMatch() but the result is the same – no rows.

What seems strange:

• The exact same script with IntervalMatch works in QlikView, but fails silently in Qlik Sense.

• It appears to load structure (columns) but no content.

Has anyone experienced similar behavior with IntervalMatch() after migrating to Qlik Sense? Are there best practices or known differences I should be aware of?

I’ll attach a simplified part of the script to clarify.

Sub Formulate_Account_Grouping

/*

Actually we want to group accounts by 4 levels or categories

starting from level 3

*/

/*

these are the accounts that we want per company

*/

account_level:

LOAD distinct 

GMAID as account_row_id,

GMCO as company_id,

GMOBJ as account_num

FROM

[lib://QVD/Tables/PRODDTA.F0901.qvd]

(qvd)

where  GMCO <> 99999

and wildmatch(GMLDA ,6,7,8)

and (GMPEC <> 'N' or GMOBJ=4999);

//and GMPEC <> 'N';

/*************************************************************************************************/

/*

let's formulate the starting level which is level 3

*/

temp_acc_level_3:

LOAD distinct 

GMCO as company_id,

GMOBJ as acc_num_3,

GMDL01 as acc_level_3

from [lib://QVD /Tables/PRODDTA.F0901.qvd]

(qvd)

where  

GMCO <> 99999

and  GMLDA = 3

and GMPEC = 'N';

temp_acc_level_3_2:

NoConcatenate

LOAD * Resident temp_acc_level_3

Order by company_id asc,acc_num_3 desc;

drop Table temp_acc_level_3;

temp_acc_level_3_3:

LOAD 

        company_id,

acc_level_3,

acc_num_3, 

acc_num_3 as start_account,

if(RowNo() = 1 or company_id <> Previous(company_id),99999,Peek(start_account) -1 ) as end_account

Resident temp_acc_level_3_2;

drop Table temp_acc_level_3_2;

temp_match:

IntervalMatch(account_num) 

    LOAD start_account,end_account

Resident temp_acc_level_3_3;

INNER JOIN(account_level)

LOAD * Resident temp_match;

drop Table temp_match;

INNER JOIN(account_level)

LOAD start_account,end_account, acc_level_3,company_id,acc_num_3

Resident temp_acc_level_3_3;

drop Table temp_acc_level_3_3;

drop fields start_account,end_account;

Would greatly appreciate your input. Thanks!

[MarioGabriel]

I have tried it, but temp_match is still zero. I’ve attached my adjusted script. The table is now showing, but for example as you can see, the level 4 account should be connected to the level 3 account. I should see “Current Assets” and “Fixed Assets” here, rather than what’s currently displayed. Again, thank you for your help.

Sub Formulate_Account_Grouping

account_level:

LOAD distinct 

    GMAID as account_row_id,

    GMCO as company_id,

    GMOBJ as account_num

FROM [lib://QVD /Tables/PRODDTA.F0901.qvd] (qvd)

WHERE GMCO <> 99999

  AND wildmatch(GMLDA, 6, 7,8

  AND (GMPEC <> 'N' OR GMOBJ = 4999);

temp_acc_level_3:

LOAD distinct 

    GMCO as company_id,

    GMOBJ as acc_num_3,

    GMDL01 as acc_level_3

FROM  [lib://QVD/Tables/PRODDTA.F0901.qvd] (qvd)

WHERE GMCO <> 99999 AND GMLDA = 3 AND GMPEC = 'N';

temp_acc_level_3_2:

NoConcatenate

LOAD * RESIDENT temp_acc_level_3

ORDER BY company_id ASC, acc_num_3 DESC;

DROP TABLE temp_acc_level_3;

temp_acc_level_3_3:

LOAD 

    company_id,

    acc_level_3,

    acc_num_3, 

    acc_num_3 AS start_account,

   IF(RowNo() = 1 OR company_id <> Previous(company_id), 99999, Peek(start_account) - 1) AS end_account

RESIDENT temp_acc_level_3_2

Order by company_id, acc_num_3 ;

DROP TABLE temp_acc_level_3_2;

// debug_account_ranges:

// LOAD 

//     company_id,

//     acc_num_3,

//     acc_level_3,

//     start_account,

//     end_account

// RESIDENT temp_acc_level_3_3;

 temp_match:

intervalMatch(account_num,company_id) 

    LOAD start_account,end_account,company_id

 RESIDENT temp_acc_level_3_3

Order by company_id, acc_num_3;

INNER JOIN (account_level)

LOAD 

    start_account, 

    end_account, 

    acc_level_3, 

    company_id, 

    acc_num_3

RESIDENT temp_acc_level_3_3;

DROP TABLE temp_match;

INNER JOIN(account_level)

LOAD start_account,end_account, acc_level_3,company_id,acc_num_3

Resident temp_acc_level_3_3;

drop Table temp_acc_level_3_3;

        drop fields start_account,end_account;

store account_level into [lib://QVD /Test/account_level_test.qvd](qvd);

drop table account_level;

ENDSUB

[rafaelencinas]

I read you code and noticed :

GMCO , GMOBJ , GMDL01 in Database Definition are String. 

Interval Match only works with Numeric Values, 

The field containing the discrete numeric values to be linked to intervals.

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

Check again in you DataModel if the account_num (GMOBJ) is numeric

Or better, you can provide some data with Load Inline Tables to evaluate better you case 

Bye!

[MarioGabriel · Accepted solution]

Thank you, Rafael. I had already used the Num function, but it didn’t work until I tried Num#. Now I have changed my script to the following:

// Load Level 4+ accounts that need to be mapped to Level 3
account_level:
LOAD DISTINCT
GMAID as account_row_id,
Num#(GMCO) as company_id,
Num#(GMOBJ) as account_num
FROM [lib://QVD/Tables/PRODDTA.F0901.qvd] (qvd)
WHERE GMCO <> 99999
AND WildMatch(GMLDA, '6', '7', '8')
AND (GMPEC <> 'N' OR GMOBJ = 4999);

// Load Level 3 accounts with their descriptions
temp_acc_level_3:
LOAD DISTINCT
Num#(GMCO) as company_id,
Num#(GMOBJ) as acc_num_3,
Num#(GMDL01) as acc_level_3
FROM [lib://QVD/Tables/PRODDTA.F0901.qvd] (qvd)
WHERE GMCO <> 99999
AND GMLDA = 3
AND GMPEC = 'N';

// Sort by company and account number 
temp_acc_level_3_2:
NoConcatenate
LOAD *
RESIDENT temp_acc_level_3
ORDER BY company_id ASC, acc_num_3 desc;
DROP TABLE temp_acc_level_3;

// Create ranges for each Level 3 account
temp_acc_level_3_3:
LOAD
company_id,
acc_level_3,
acc_num_3,
acc_num_3 AS start_account,
if(RowNo() = 1 or company_id <> Previous(company_id),99999,Peek(start_account) -1 ) as end_account
RESIDENT temp_acc_level_3_2
ORDER BY company_id ASC, acc_num_3 desc;
DROP TABLE temp_acc_level_3_2;

// Create IntervalMatch for Level 3
temp_match:
IntervalMatch(account_num, company_id)
LOAD
start_account,
end_account,
company_id
RESIDENT temp_acc_level_3_3;

// Join the interval match results to account_level
LEFT JOIN(account_level)
LOAD *
RESIDENT temp_match;
DROP TABLE temp_match;

// Join the Level 3 details
LEFT JOIN(account_level)
LOAD
start_account,
end_account,
acc_level_3,
company_id,
acc_num_3
RESIDENT temp_acc_level_3_3;
DROP TABLE temp_acc_level_3_3;

// Clean up temporary fields
DROP FIELDS start_account, end_account;

The script is now working as it should. I really appreciate everyone’s help. Thank you!