"Recursive" load

Mon, 14 Jul 2014 07:58:24 GMT

Hi everyone, i have issue with loading profit&loss table. I load strucute of profit&loss table from 3 different tables and i have table with account details (amount, id etc). I would like to connect profit&loss and accounts via accoundID (in my code named Suau). But i have problem wiht calculated lines. I do not know how to explain this problem so I attached an example.

Example of what i have:

FS_Caption	LineNumber	AccId_Line	AccountID
Revenue from goods.	1	378	378
Cost of goods sold	2	411	411
Cost of goods sold	2	430	430
Gross profit	3	1 (this is not really account id...)	-
Gross profit	3	2 (because it has less than tree numbers)	-

and i need to transform Gross profit to something like this.

FS_Caption	LineNumber	Accid_Line	AccountID
Gross profit	3	378	378
Gross profit	3	411	411
Gross profit	3	430	430

Here is my code:

QUALIFY *;
UNQUALIFY Suau;
PaL:
LOAD
 Text,
 AccId_Line,
 Suau,
 Poradi as CalculationOrder,
 Ozn as LineOrder
SQL
    SELECT $(Omezeni)
    VYPOCTY.Poradi,
    TEXTY.Text,
    UCTY2.Suau,
    UCTY2.Ci,
    AccId_Line,
    Ozn
    FROM VSL_POR VYPOCTY
    LEFT JOIN VSL_SLP TEXTY on VYPOCTY.CiR = TEXTY.CiR
    LEFT JOIN VSL_UC UCTY1 on VYPOCTY.CiR = UCTY1.CiR
    LEFT JOIn VIEWUCOS UCTY2 on UCTY1.AccId_Line=UCTY2.Ci
    WHERE Cih=4 and UCTY2.Ci iS NOT NULL
    ORDER BY Poradi;

Can someone please help me?

topic "Recursive" load in QlikView

"Recursive" load