topic Re: Looping in QlikView

Looping

Wed, 12 Aug 2015 13:29:51 GMT

I am working on taking over an established application and have been tasked with adding another data source to the application. The original data source sorts invoices by a number of factors (amount spent, tax, vendor, etc.) and my new data source includes the same invoice number along with a couple of other keys that can be used to create a unique key. However, as soon as I upload my data it tells me I have a loop.

In the data below the BELNR, BUKRS and GJAHR field make a unique ID.

I add this to the bottom of my script. My load statement works until I write "as BUKRS" upon which I get an error message saying I have a loop. But I need this field to connect with the earlier tables. Does anyone know why it loops on BUKRS and how I can fix it (join the MIPSSAPInvoice table to the other tables?

MipsSAPInvoice:

LOAD ProjectNo,
     sap_invoice_id,
     invoice_id,
     sap_ak_company_no as BUKRS,
     invoice_voucher_no,
     sap_invoice_date_payed
FROM (qvd);

The script above:

TempHeaderTableSAP:

Load
    BELNR & BUKRS & GJAHR as %DocKey,
    BELNR,
    BLART,
    BUKRS,
    GJAHR,
    HWAER,
    UseRate,
    MONAT,
    UKURS
FROM (qvd)
WHERE GJAHR > 2010 AND BLART <> 'KZ' AND MANDT = 101 AND BLART <> 'ZP';

Left Join
TempHeaderItemSAP:
LOAD
    BELNR & BUKRS & GJAHR as %DocKey,
    BELNR & POSID as %MIPSDocKey,
    BELNR,
    BUKRS,
    BUZEI,
    DMBTR,
    GJAHR,
    KOSTL,
    KSTAR,
    LIFNR,
    MANDT,
    MWSTS,
    POSID,
    project_code,
    PRCTR,
    SGTXT,
    SHKZG
FROM (qvd)
WHERE GJAHR > 2010 AND MANDT = 101;

ReadyTable:

// Multiplying with -1 for all rows where Debit/Credit is set to 'S'
Load *,
If(SHKZG = 'S', tempSpend_NOK * -1, tempSpend_NOK) As #ActualSpend_NOK,
If(SHKZG = 'S', tempTax_NOK * -1, tempTax_NOK) As #ActualTax_NOK,
If(SHKZG = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As #ActualNetSpend_NOK,
If(DEB_CRED2 = 'S', tempSpend_NOK * -1, tempSpend_NOK) as #ProjActualSpend_NOK,
If(DEB_CRED2 = 'S', tempTax_NOK * -1, tempTax_NOK) as #ProjActualTax_NOK,
If(DEB_CRED2 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) as #ProjActualNetSpend_NOK;

Load *,
tempSpend_NOK - tempTax_NOK As tempNetSpend_NOK;

Load *,
UseRate * DMBTR As tempSpend_NOK,
UseRate * MWSTS As tempTax_NOK;

Load *,
If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2
Resident TempHeaderTableSAP;
Store ReadyTable Into C:\QlikView\Fact.qvd (qvd);
Drop table TempHeaderTableSAP;

Left Join
Load
%DocKey,
LIFNR as LIFNR2
Resident ReadyTable
Where Len(Trim(LIFNR)) >0;

ProjectSumTable:
LOAD
      company_code as BUKRS,
      [project_code],
      [project_name],
      [profit_center],
      [customer_id]
FROM C:\QlikView\Project.qvd (qvd);

Here is my error message:

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

Re: Looping

Wed, 12 Aug 2015 13:33:32 GMT

I forgot to mention that in MIPS these are the sap_ak_company_no is the BUKRS field and invoice_voucher_no is the same as BELNR and the first 4 numbers of sap_invoice_date_payed is the GJAHR field.

Re: Looping

jonathandienst — Wed, 12 Aug 2015 13:45:36 GMT

It is probably related to the composite (synthetic) key between ProjectSumTable and ReadyTable (on BUKRS and project_code. You could either exclude the project_code from one of the two tables or rename it in one table.

Re: Looping

jonas_rezende — Wed, 12 Aug 2015 19:53:21 GMT

Hi, espenlutken‌.

See if the adjustments help, bold, or at least opens the way for the solution.

TempHeaderTableSAP:

Load

BELNR & BUKRS & GJAHR as %DocKey,

BELNR,

BLART,

BUKRS,

GJAHR,

HWAER,

UseRate,

MONAT,

UKURS

FROM (qvd)

WHERE GJAHR > 2010 AND BLART <> 'KZ' AND MANDT = 101 AND BLART <> 'ZP';

Left Join

TempHeaderItemSAP:

LOAD

BELNR & BUKRS & GJAHR as %DocKey,

BELNR & POSID as %MIPSDocKey,

BELNR,

BUKRS,

BUZEI,

DMBTR,

GJAHR,

KOSTL,

KSTAR,

LIFNR,

MANDT,

MWSTS,

POSID,

project_code,

PRCTR,

SGTXT,

SHKZG

FROM (qvd)

WHERE GJAHR > 2010 AND MANDT = 101;

NoConcatenate

ReadyTable:

// Multiplying with -1 for all rows where Debit/Credit is set to 'S'

Load *,

If(SHKZG = 'S', tempSpend_NOK * -1, tempSpend_NOK) As #ActualSpend_NOK,

If(SHKZG = 'S', tempTax_NOK * -1, tempTax_NOK) As #ActualTax_NOK,

If(SHKZG = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As #ActualNetSpend_NOK,

If(DEB_CRED2 = 'S', tempSpend_NOK * -1, tempSpend_NOK) as #ProjActualSpend_NOK,

If(DEB_CRED2 = 'S', tempTax_NOK * -1, tempTax_NOK) as #ProjActualTax_NOK,

If(DEB_CRED2 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) as #ProjActualNetSpend_NOK;

Load *,

tempSpend_NOK - tempTax_NOK As tempNetSpend_NOK;

Load *,

UseRate * DMBTR As tempSpend_NOK,

UseRate * MWSTS As tempTax_NOK;

Load *,

If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2

Resident TempHeaderTableSAP;

Store ReadyTable Into C:\QlikView\Fact.qvd (qvd);

Drop tables

TempHeaderTableSAP,

ReadyTable;

ReadyTable:

LOAD

%DocKey,

%MIPSDocKey,

BELNR,

BLART,

BUKRS,

GJAHR,

HWAER,

UseRate,

MONAT,

UKURS,

BUZEI,

DMBTR,

KOSTL,

KSTAR,

LIFNR,

MANDT,

MWSTS,

POSID,

project_code,

PRCTR,

SGTXT,

SHKZG,

#ActualSpend_NOK,

#ActualTax_NOK,

#ActualNetSpend_NOK,

#ProjActualSpend_NOK,

#ProjActualTax_NOK,

#ProjActualNetSpend_NOK,

tempNetSpend_NOK,

tempSpend_NOK,

tempTax_NOK

FROM

C:\QlikView\Fact.qvd

(qvd);

Left Join (ReadyTable)

Load

%DocKey,

LIFNR as LIFNR2

FROM

C:\QlikView\Fact.qvd

(qvd)

Where Len(Trim(LIFNR)) >0;

ProjectSumTable:

LOAD

company_code as BUKRS,

// [project_code], //See if is needed create Composite Key with BUKRS.

[project_name],

[profit_center],

[customer_id]

FROM C:\QlikView\Project.qvd (qvd);

MipsSAPInvoice:

LOAD ProjectNo,

sap_invoice_id,

invoice_id,

sap_ak_company_no as BUKRS,

invoice_voucher_no,

sap_invoice_date_payed

FROM (qvd);

Hope this helps!

Re: Looping

Thu, 13 Aug 2015 13:24:19 GMT

Hi,

Unfortunately I haven't been able to test these out in all their forms, but am working on the solution. I will mark this as correct for now and establish a new discussion if I am not able to work it out. Thanks for the help! 🙂