Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rdlccn70
Contributor III
Contributor III

Load Table Resident ?

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.

Load.png

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

1 Solution

Accepted Solutions
olivierrobin
Specialist III
Specialist III

you need customer, it's the key

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

View solution in original post

9 Replies
olivierrobin
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

Hi Olivier, i have updated my question.

olivierrobin
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

Ros_no_value.png

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

olivierrobin
Specialist III
Specialist III

sorry, problem with

concatenate(INPUT):

suppress the ':' at end

olivierrobin
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

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
Specialist III
Specialist III

you need customer, it's the key

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

rdlccn70
Contributor III
Contributor III
Author

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;