topic Re: Load Table Resident ? in QlikView

Load Table Resident ?

rdlccn70 — Thu, 28 Jun 2018 13:32:30 GMT

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

Re: Load Table Resident ?

olivierrobin — Thu, 28 Jun 2018 13:53:45 GMT

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

Re: Load Table Resident ?

rdlccn70 — Thu, 28 Jun 2018 15:10:33 GMT

Hi Olivier, i have updated my question.

Re: Load Table Resident ?

olivierrobin — Thu, 28 Jun 2018 15:15:22 GMT

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;

Re: Load Table Resident ?

rdlccn70 — Thu, 28 Jun 2018 15:28:52 GMT

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

Re: Load Table Resident ?

olivierrobin — Thu, 28 Jun 2018 15:30:41 GMT

sorry, problem with

concatenate(INPUT):

suppress the ':' at end

Re: Load Table Resident ?

olivierrobin — Thu, 28 Jun 2018 15:34:15 GMT

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

Re: Load Table Resident ?

rdlccn70 — Thu, 28 Jun 2018 15:47:29 GMT

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');

Re: Load Table Resident ?

olivierrobin — Thu, 28 Jun 2018 16:02:57 GMT

you need customer, it's the key

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

Re: Load Table Resident ?

rdlccn70 — Thu, 28 Jun 2018 16:06:08 GMT

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;