Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

saviostrazzullo
New Contributor

New table with less rows

Hi,

I have a table Table1 with 3 rows (A,B,Costs):

  

Table1
Settore PL
A 30
B 20
Costs (10)

I have to produce a new table Table2 like this:

  

Table2
Settore PL
A 22
B 18

where

Table2.A = Table1.A + 80% Table1.Costs

Table2.B = Table1B + 20% Table1.Costs

How can I create the new Table2?

Thanks!

Savio

1 Solution

Accepted Solutions

Re: New table with less rows

Try this

Table:

LOAD *,

Pick(Match(Settore, 'A', 'B'), 0.80, 0.20) as PercAlloc;

LOAD * INLINE [

    Settore, PL

    A, 30

    B, 20

    Costs, -10

];


Left Join (Table)

LOAD PL as PL_New

Resident Table

Where Settore = 'Costs';


FinalTable:

LOAD Settore,

(PL + (PL_New*PercAlloc)) as PL

Resident Table

Where Settore <> 'Costs';


DROP Table Table;

6 Replies

Re: New table with less rows

front end? back end? how is the cost %age determined?

saviostrazzullo
New Contributor

Re: New table with less rows

I would like insert it in a script in the data load editor.

I need to create the table 2 adding costs. I have the total costs in the row in the table 1.

In the table 2 I need to add cost  The total costs (-10) needs to be added 80% to Settore A and 20% to Settore B.

The PL field of the table 2 is for :

PL Settore A = 30 + (80% * (- 10))

PL Settore B = 20 + (20%* (-10))

Thanks!

Re: New table with less rows

But where are the 80% and 20% number coming from? They are always 80% and 20%? What if there is a C? What would be the percentage? or there is always A and B?

saviostrazzullo
New Contributor

Re: New table with less rows

I have to apply costs only when Settore = A or Settore = B. If Settore = C or other I haven't costs.

The percentage 80% and 20% are constants for Settore = A (80%) and Settore = B (20%) I will specify them in the script or insert in a table.

I have never added or reduced rows in a table. Maybe I need to understand how I can add o reduce rows and calculate new values for the output table.

Can you help me?

Thanks!

Re: New table with less rows

Try this

Table:

LOAD *,

Pick(Match(Settore, 'A', 'B'), 0.80, 0.20) as PercAlloc;

LOAD * INLINE [

    Settore, PL

    A, 30

    B, 20

    Costs, -10

];


Left Join (Table)

LOAD PL as PL_New

Resident Table

Where Settore = 'Costs';


FinalTable:

LOAD Settore,

(PL + (PL_New*PercAlloc)) as PL

Resident Table

Where Settore <> 'Costs';


DROP Table Table;

saviostrazzullo
New Contributor

Re: New table with less rows

Great! It works!

Thank you very much!

Community Browser