Resident loads and inner Joins

[#1]

I have this sample code, not sure where I am going wrong. I am going wrong somewhere at the Inner Join.

I have attached the sample data.

What I want to do is pivot the 2 Amount columns to the right for each value of Days. So Days  0 = CurrentAmount, 1 = PreviousAmount, 2 = 30Days. This is Ageing Data. Eventually at the end I want to delete the InputTable and just work with the Outputtable which will then have 60 Values for Amount and 60 Values for AmountVat. Our Clients then need this in this format in a qvd file, but that I can do. We could be summing the values for years 1, 2 3 4 etc, but for now I just need to pivot the data. This is just a sample so ignore that it is coming from xls. We get the data from SQL. What is the most effective way of doing this, and it my way is OK what am I missing on the scripting?

InputTable:

LOAD ID,

     Period,

     AccountNumber,

     Detail1,

     Detail2,

     Amount,

     AmountVAT,

     Days

FROM

(ooxml, embedded labels, table is Sheet1);

OutputTable:

NoConcatenate LOAD DISTINCT ID as RID,

     Period as RPeriod,

     AccountNumber as RAccountNumber,

     Detail1 as RDetail1,

     Detail2 as RDetail2

Resident InputTable;

Inner Join InputTable

Load

ID as RID,

     Amount as RCurrentAmount,

     AmountVAT as RCurrentAmountVAT

     Where Days = '0' ;

Inner Join InputTable

Load

ID as RID,

     Amount as RPreviousAmount,

     AmountVAT as RPreviousAmountVAT

     Where Days = '1' ;

[sunny_talwar]

I think you missed the parenthesis around the table name after the Inner Join keywords

InputTable:

LOAD ID,

     Period,

     AccountNumber,

     Detail1,

     Detail2,

     Amount,

     AmountVAT,

     Days

FROM

[Example Pivoting import.xlsx]

(ooxml, embedded labels, table is Sheet1);

OutputTable:

NoConcatenate LOAD DISTINCT ID as RID,

     Period as RPeriod,

     AccountNumber as RAccountNumber,

     Detail1 as RDetail1,

     Detail2 as RDetail2

Resident InputTable;

Inner Join (InputTable)

Load ID as RID,

     Amount as RCurrentAmount,

     AmountVAT as RCurrentAmountVAT

Resident InputTable

Where Days = '0' ;

Inner Join (InputTable)

Load ID as RID,

     Amount as RPreviousAmount,

     AmountVAT as RPreviousAmountVAT

Resident InputTable

Where Days = '1' ;

UPDATE: But what are you inner joining on? None of the field names match, wha

[#3]

Something as silly as that thanks.

Is the Jon not correct on ID?

[#4]

I have it like this now but its adding the columns to InputTable- The Inner Join need to be to OutputTable needs to be OutputTable , Maybe I am not getting this, why?

InputTable:

LOAD ID,

     Period,

     AccountNumber,

     Detail1,

     Detail2,

     Amount,

     AmountVAT,

     Days

FROM

(ooxml, embedded labels, table is Sheet1);

OutputTable:

NoConcatenate LOAD DISTINCT ID ,

     Period as RPeriod,

     AccountNumber as RAccountNumber,

     Detail1 as RDetail1,

     Detail2 as RDetail2

Resident InputTable;

Inner Join (InputTable) -- this needs to bo OutputTable

Load

ID ,

     Amount as RCurrentAmount,

     AmountVAT as RCurrentAmountVAT

     Resident InputTable

     Where Days = '0' ;

[sunny_talwar · Accepted solution]

Then may be this:

InputTable:

LOAD ID,

    Period,

    AccountNumber,

    Detail1,

    Detail2,

    Amount,

    AmountVAT,

    Days

FROM

[Example Pivoting import.xlsx]

(ooxml, embedded labels, table is Sheet1);

OutputTable:

NoConcatenate LOAD DISTINCT ID as RID,

    Period as RPeriod,

    AccountNumber as RAccountNumber,

    Detail1 as RDetail1,

    Detail2 as RDetail2

Resident InputTable;

Inner Join (OutputTable)

Load ID as RID,

    Amount as RCurrentAmount,

    AmountVAT as RCurrentAmountVAT

Resident InputTable

Where Days = '0' ;

Inner Join (OutputTable)

Load ID as RID,

    Amount as RPreviousAmount,

    AmountVAT as RPreviousAmountVAT

Resident InputTable

Where Days = '1' ;