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