Load Table Resident ?

[rdlccn70]

Hi,

I have a table like below (INPUT) and i would like add in Column Ratio the line with ROS (Result/Revenue).

How can i do this, so that i have the OUTPUT Table.

I have tried this, but there is a mistake:

INPUT:
LOAD Customer,
Ratio,
Amount
FROM
(ooxml, embedded labels, table is Tabelle2);


INPUT_TEMP:

LOAD
Customer,
Ratio,
Amount as Amount_Revenue

resident INPUT
WHERE match(Ratio, 'Revenue');


JOIN (INPUT_TEMP)

INPUT_TEMP:

LOAD

Customer,
Ratio,
Amount as Amount_Result

resident INPUT
WHERE match(Ratio, 'Result');



INPUT:
LOAD

Customer,
'ROS' as Ratio,
Amount_Result / Amount_Revenue * 100  as Amount

resident INPUT_TEMP;

drop table INPUT_TEMP;

Regards,

Erdal

Message was edited by: Erdal Cücen

[olivierrobin]

hello

you could do something like

1 - load INPUT where Ratio='Revenue'

2 - left join previous table load INPUT where Ratio='Result'

3 - LOAD INPUT

4 - append the 1st table with amount=rev/res

[rdlccn70]

Hi Olivier, i have updated my question.

[olivierrobin]

and what's the mistake ?

I have changed your code :

INPUT:

noconcatenate

LOAD Customer,

Ratio,

Amount

FROM

(ooxml, embedded labels, table is Tabelle2);

INPUT_TEMP:

noconcatenate

LOAD

Customer,

Ratio,

Amount as Amount_Revenue

resident INPUT

WHERE match(Ratio, 'Revenue');

left JOIN (INPUT_TEMP)

LOAD

Customer,

Amount as Amount_Result

resident INPUT

WHERE match(Ratio, 'Result');

concatenate(INPUT):

LOAD

Customer,

'ROS' as Ratio,

Amount_Result / Amount_Revenue * 100  as Amount

resident INPUT_TEMP;

drop table INPUT_TEMP;

[rdlccn70]

my Skript Shows no values in the line ROS, i have also tried with Noconcanate. Your Code have not the line ROS.

[olivierrobin]

sorry, problem with

concatenate(INPUT):

suppress the ':' at end

[olivierrobin]

in your case, the problem comes from the join . in a join you should precise ONLY the keys (that means all fields with common name in the 2 tables) and the dimensions you want to add

[rdlccn70]

It works , can i delete customer in the left join table or do i Need customer as key?

left JOIN (INPUT_TEMP)

LOAD

//Customer,
Amount as Amount_Result

resident INPUT

WHERE match(Ratio, 'Result');

[olivierrobin · Accepted solution]

you need customer, it's the key

Don't forget to mark the response as correct and close the post

[rdlccn70]

Ok Thanks, here the correct Code:

Regards,

Erdal

INPUT:

noconcatenate

LOAD
Customer,
Ratio,
Amount

FROM

(ooxml, embedded labels, table is Tabelle2);



INPUT_TEMP:

noconcatenate

LOAD

Customer,
Ratio,
Amount as Amount_Revenue

resident INPUT

WHERE match(Ratio, 'Revenue');



left JOIN (INPUT_TEMP)

LOAD

Customer,
Amount as Amount_Result

resident INPUT

WHERE match(Ratio, 'Result');


concatenate(INPUT)

LOAD

Customer,
'ROS' as Ratio,
Amount_Result / Amount_Revenue * 100  as Amount


resident INPUT_TEMP;

drop table INPUT_TEMP;