Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rdlccn70
New 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

Tags (3)
1 Solution

Accepted Solutions
olivierrobin
Valued Contributor II

Re: Load Table Resident ?

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
Valued Contributor II

Re: Load Table Resident ?

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

Re: Load Table Resident ?

Hi Olivier, i have updated my question.

olivierrobin
Valued Contributor II

Re: Load Table Resident ?

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

Re: Load Table Resident ?

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
Valued Contributor II

Re: Load Table Resident ?

sorry, problem with

concatenate(INPUT):

suppress the ':' at end

olivierrobin
Valued Contributor II

Re: Load Table Resident ?

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

Re: Load Table Resident ?

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
Valued Contributor II

Re: Load Table Resident ?

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

rdlccn70
New Contributor III

Re: Load Table Resident ?

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;