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?
