Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sinanozdemir
Valued Contributor III

Load script skips a table during the script execution process

Hi all,

I have been having an issue that I couldn't figure out the root cause of it. I am trying to load two tables VM_CLAIM and VM_MEMBMTHS, and they both reside in the same database so that the one ODBC connection is used. Also "Force 32 Bit" option is checked.

What happens is that the first table VM_CLAIM gets loaded, but the second table VM_MEMBMTHS doesn't. Almost QlikView skips the second table for some reasons. I checked the availability of VM_MEMBMTHS table in both SQL Server and another BI tool, the table runs perfectly.

Here is a screenshot of the script execution progress window:

Capture.PNG

There are two different tables in the load script:

Capture.PNG Capture.PNG

I also checked if the tables are being concatenated, and there is only one common field in both tables so I don't think it is a concatenation issue.

If anyone had this issue and resolved it, I would appreciate your feedback.

Thank you

pbutterw

Tags (1)
14 Replies
andrespa
Valued Contributor

Re: Load script skips a table during the script execution process

Hi Sinan, does your script finish or just break at that point in the execution?

awhitfield
Esteemed Contributor

Re: Load script skips a table during the script execution process

Hi,

can you supply your script details?

Andy

sinanozdemir
Valued Contributor III

Re: Load script skips a table during the script execution process

Hi Andres,

It doesn't break, it just stops.

Thanks

sinanozdemir
Valued Contributor III

Re: Load script skips a table during the script execution process

Hi Andy,

Sure. Here is the script of the problematic table:

MM:

LOAD

  MM_UNITS,

  PREMIUM_EMPLOYER_PAID,

  PREMIUM_EMPLOYEE_PAID,

  INCURRED_YEAR_AND_MONTH & '|' & CCHG_CAT_CODE_AND_DESC & '|' & PRODUCT_TYPE & '|' & OWNER_HOSPITAL As Key;

LOAD "INCURRED_YEAR_AND_MONTH" As MM_DATE_TEXT,

    Sum("MM_UNITS") As MM_UNITS,

    "PRODUCT_TYPE" As MM_BENFT_PACK,

    "OWNER_HOSPITAL" As MM_OWNER,

    Sum("PREMIUM_EMPLOYER_PAID") As PREMIUM_EMPLOYER_PAID,

    Sum("PREMIUM_EMPLOYEE_PAID") As PREMIUM_EMPLOYEE_PAID,

    "CCHG_CAT",

    "CCHG_DESC",

    "CCHG_CAT_CODE_AND_DESC" AS MM_CCHG_COMBINED

Where INCURRED_YEAR_AND_MONTH >= '201501' And MM_UNITS = 1

Group By

  INCURRED_YEAR_AND_MONTH,

  PRODUCT_TYPE,

  OWNER_HOSPITAL,

  CCHG_CAT,

  CCHG_DESC,

  CCHG_CAT_CODE_AND_DESC;

SQL SELECT "INCURRED_YEAR_AND_MONTH",

    "MM_UNITS",

    "PRODUCT_TYPE",

    "OWNER_HOSPITAL",

    "PREMIUM_EMPLOYER_PAID",

    "PREMIUM_EMPLOYEE_PAID",

    "CCHG_CAT",

    "CCHG_DESC",

    "CCHG_CAT_CODE_AND_DESC",

    "MEMBER_COMPANY"

FROM MedInsight2.USR."VW_MEMBMTHS";

I also tried to load the data without the aggregation and no luck.

Thanks

andrespa
Valued Contributor

Re: Load script skips a table during the script execution process

What happens if you just run this part of the script: ??

  1. SQL SELECT "INCURRED_YEAR_AND_MONTH"
  2.     "MM_UNITS"
  3.     "PRODUCT_TYPE"
  4.     "OWNER_HOSPITAL"
  5.     "PREMIUM_EMPLOYER_PAID"
  6.     "PREMIUM_EMPLOYEE_PAID"
  7.     "CCHG_CAT"
  8.     "CCHG_DESC"
  9.     "CCHG_CAT_CODE_AND_DESC"
  10.     "MEMBER_COMPANY" 
  11. FROM MedInsight2.USR."VW_MEMBMTHS";
sinanozdemir
Valued Contributor III

Re: Load script skips a table during the script execution process

Hi Andres,

That seems to be running fine. Although, why would this happen with the preceding load?

Thanks

andrespa
Valued Contributor

Re: Load script skips a table during the script execution process

Try this one: I think the problem is happening because your double preceding load. So hopefuly this one should work fine.

  1. MM:
  2. LOAD *, 
  3. INCURRED_YEAR_AND_MONTH & '|' & CCHG_CAT_CODE_AND_DESC & '|' & PRODUCT_TYPE & '|' & OWNER_HOSPITAL As Key;
  4. "INCURRED_YEAR_AND_MONTH" As MM_DATE_TEXT, 
  5.     Sum("MM_UNITS") As MM_UNITS, 
  6.     "PRODUCT_TYPE" As MM_BENFT_PACK, 
  7.     "OWNER_HOSPITAL" As MM_OWNER, 
  8.     Sum("PREMIUM_EMPLOYER_PAID") As PREMIUM_EMPLOYER_PAID, 
  9.     Sum("PREMIUM_EMPLOYEE_PAID") As PREMIUM_EMPLOYEE_PAID, 
  10.     "CCHG_CAT"
  11.     "CCHG_DESC"
  12.     "CCHG_CAT_CODE_AND_DESC" AS MM_CCHG_COMBINED 
  13. Where INCURRED_YEAR_AND_MONTH >= '201501' And MM_UNITS = 1 
  14. Group By 
  15.   INCURRED_YEAR_AND_MONTH, 
  16.   PRODUCT_TYPE, 
  17.   OWNER_HOSPITAL, 
  18.   CCHG_CAT, 
  19.   CCHG_DESC, 
  20.   CCHG_CAT_CODE_AND_DESC; 
  21. SQL SELECT "INCURRED_YEAR_AND_MONTH"
  22.     "MM_UNITS"
  23.     "PRODUCT_TYPE"
  24.     "OWNER_HOSPITAL"
  25.     "PREMIUM_EMPLOYER_PAID"
  26.     "PREMIUM_EMPLOYEE_PAID"
  27.     "CCHG_CAT"
  28.     "CCHG_DESC"
  29.     "CCHG_CAT_CODE_AND_DESC"
  30.     "MEMBER_COMPANY" 
  31. FROM MedInsight2.USR."VW_MEMBMTHS";

Re: Load script skips a table during the script execution process

It is commonly known (very recently by me as well, although I've been fighting them for some time now) that Preceding Loads should be used for doing just minor column and value manipulations. As soon as you start doing extra filtering (or GROUP BYs) you may unexpectedly run into trouble.

You can continue doing what Andres initiated. Add the next Preceding load and see what you get. If that doesn't work, try a conversion into successive RESIDENT LOADS. That should work in any case or will produce at least proper error messages if it doesn't.

Peter

alexdataiq
Contributor III

Re: Load script skips a table during the script execution process

Hi Sinan,

Does the scripts ends (the close button is active) or it just hangs? If it hangs maybe it taking a long time for the QV engine to build the data model.

Have you tried using the prefix Noconcatenate when loading your second table?

What does the document log says?

Regards

Community Browser