Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i load some data via a cross table, which works fine, i now want a second stage to the transform so that it edits the PYFY and YTD column so that they are filled with 0 for all months except CY12. See examples below for how it looks and how the output i want to look like.
this is the data i am loading
Financial_Line | cross_selling_key | PYFY | YTD3 | CY01 | CY02 | CY03 | CY04 | CY05 | CY06 | CY07 | CY08 | CY09 | CY10 | CY11 | CY12 |
LTP (Maturity) - Asset | CASH | 697.24 | 1150.44 | 72.11 | 77.22 | 83.13 | 95.89 | 98.96 | 101.71 | 110.26 | 105.29 | 105.25 | 103.85 | 97.78 | 98.99 |
and i transform using the code
temp:
CrossTable(Date, Amount, 4)
LOAD Financial_Line,
cross_selling_key,
PYFY,
YTD3,
CY01,
CY02,
CY03,
CY04,
CY05,
CY06,
CY07,
CY08,
CY09,
CY10,
CY11,
CY12
FROM
data.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
How it looks
Date | cross_selling_key | Financial_Line | Amount | PYFY | YTD3 |
---|---|---|---|---|---|
CY01 | CASH | LTP (Maturity) - Asset | 72.11 | 697.24 | 1150.44 |
CY02 | CASH | LTP (Maturity) - Asset | 77.22 | 697.24 | 1150.44 |
CY03 | CASH | LTP (Maturity) - Asset | 83.13 | 697.24 | 1150.44 |
CY04 | CASH | LTP (Maturity) - Asset | 95.89 | 697.24 | 1150.44 |
CY05 | CASH | LTP (Maturity) - Asset | 98.96 | 697.24 | 1150.44 |
CY06 | CASH | LTP (Maturity) - Asset | 101.71 | 697.24 | 1150.44 |
CY07 | CASH | LTP (Maturity) - Asset | 110.26 | 697.24 | 1150.44 |
CY08 | CASH | LTP (Maturity) - Asset | 105.29 | 697.24 | 1150.44 |
CY09 | CASH | LTP (Maturity) - Asset | 105.25 | 697.24 | 1150.44 |
CY10 | CASH | LTP (Maturity) - Asset | 103.85 | 697.24 | 1150.44 |
CY11 | CASH | LTP (Maturity) - Asset | 97.78 | 697.24 | 1150.44 |
CY12 | CASH | LTP (Maturity) - Asset | 98.99 | 697.24 | 1150.44 |
after transform i would like my output table to look like thi. I want the data changed in teh transform not set analysis.
Date | cross_selling_key | Financial_Line | Amount | PYFY | YTD3 |
CY01 | CASH | LTP (Maturity) - Asset | 72.11 | 0 | 0 |
CY02 | CASH | LTP (Maturity) - Asset | 77.22 | 0 | 0 |
CY03 | CASH | LTP (Maturity) - Asset | 83.13 | 0 | 0 |
CY04 | CASH | LTP (Maturity) - Asset | 95.89 | 0 | 0 |
CY05 | CASH | LTP (Maturity) - Asset | 98.96 | 0 | 0 |
CY06 | CASH | LTP (Maturity) - Asset | 101.71 | 0 | 0 |
CY07 | CASH | LTP (Maturity) - Asset | 110.26 | 0 | 0 |
CY08 | CASH | LTP (Maturity) - Asset | 105.29 | 0 | 0 |
CY09 | CASH | LTP (Maturity) - Asset | 105.25 | 0 | 0 |
CY10 | CASH | LTP (Maturity) - Asset | 103.85 | 0 | 0 |
CY11 | CASH | LTP (Maturity) - Asset | 97.78 | 0 | 0 |
CY12 | CASH | LTP (Maturity) - Asset | 98.99 | 697.24 | 1150.44 |
If anyone can help i would be grateful, and if you could post the code in here rather than an example i would be very grateful.
Cheers
it is kind a stupid idea but if your data set is small try to make two separate load :
temp:
CrossTable(Date, Amount, 4)
LOAD Financial_Line,
cross_selling_key,
num(0) as PYFY,
num(0) as YTD3,
CY01,
CY02,
CY03,
CY04,
CY05,
CY06,
CY07,
CY08,
CY09,
CY10,
CY11
FROM
(ooxml, embedded labels, table is tab);
CrossTable(Date, Amount, 4)
LOAD Financial_Line,
cross_selling_key,
PYFY,
YTD3,
CY12
FROM
(ooxml, embedded labels, table is tab);