topic Re: Issue Migrating IntervalMatch from QlikView to Qlik Sense – Only Field Names, No Data Returned in App Development

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

MarioGabriel — Fri, 16 May 2025 12:03:30 GMT

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!

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

-SW- — Wed, 28 May 2025 22:16:08 GMT

I have encountered similar problems before and in my case it was how Qlik Sense was referring my fields as text as opposed to numbers. Can you try and force all relevant fields with Num() and try again?

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

MarioGabriel — Thu, 29 May 2025 13:08:33 GMT

Thank you for your reply, but I have already tried it and still didn’t work.

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

Mark_Little — Thu, 29 May 2025 13:27:42 GMT

I have not seen an issue with the interval match it should work the same (And has for me).

I have seen different behaviour on the Peek or Previous (Previous I believe, sorry can't remember).

I would check this table is getting created as expected

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;

Mark

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

rafaelencinas — Mon, 02 Jun 2025 01:31:46 GMT

Reading your code:

1 - Order by your code here:

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

;

2 - Reload all tables, checking DataTypes of each field to avoid mistakes with math operations in Where Clause

Bye!

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

MarioGabriel — Mon, 02 Jun 2025 10:42:41 GMT

Hi Mark,

Thank you for your reply. The issue lies with this table: as you can see, the table is returning zeros. I have tried nearly everything, but I still am not getting any data.

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

-SW- — Mon, 02 Jun 2025 11:15:57 GMT

can you show some example values of

account_num in one table and

start_account,end_account

in another table?

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

MarioGabriel — Mon, 02 Jun 2025 14:08:44 GMT

Hello. Here are the two tables: the start_account column is interpreted as text (tagged $ascii $text), while end_account is numeric but displays many “–” entries (nulls).

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

MarioGabriel — Mon, 02 Jun 2025 14:13:24 GMT

Thank you Rafael for the suggestion. I did add the ORDER BY company_id, acc_num_3 clause to the resident load exactly as you showed, but the end_account values are still not lining up the way I expect. I also reloaded everything and verified that company_id and acc_num_3 are numeric, so the sort should work but it isn’t.

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

-SW- — Mon, 02 Jun 2025 15:52:52 GMT

can you try adding CompanyID to the intervalmatch?

temp_match:

IntervalMatch(account_num,company_id)

LOAD start_account,end_account,company_id

Resident temp_acc_level_3_3;

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

MarioGabriel — Tue, 03 Jun 2025 09:38:22 GMT

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

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

rafaelencinas — Tue, 03 Jun 2025 10:31:18 GMT

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/ScriptPrefixes/IntervalMatch.htm

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!

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

MarioGabriel — Wed, 04 Jun 2025 07:31:40 GMT

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!