Join calculated fields in script

[PBRM]

Dear all,

I want to do some calculations in the script, but I can't make it work.  I have some data like:

date             System     Value

1-1-2020    A                  12000

1-1-2020    B                  13005

1-1-2020    C                    9500

1-1-2020    D                   34321

2-1-2020    A                   13000

2-1-2020    B                   13255

2-1-2020    C                     9000

2-1-2020    D                    14000

My script looks something like:

Data:
LOAD date , 
            System, 
            value,
            if(System='A',value) as Sys1,
            if(System='C',value) as Sys2,
FROM....

Calculation:

LOAD date,

            12*Sys1/(1+Sys2) as Output

Resident Data;

No calculations were made. If I make a table, it looks like:

date                Sys1        Sys2

01-01-2020   12000     -

01-01-2020   -                9500

02-01-2020   13000     -

02-01-2020    -              90000

I think it has something to do with the way I scripted this. Tried to do something with Join, but it is getting me nowhere. Any suggestions?

[sunny_talwar · Accepted solution]

Try something like this

Data:
LOAD date, 
     value as Sys1
FROM....
Where System='A';

Left Join (Data)
LOAD date,
     value as Sys2
FROM....
Where System='C';

Calculation:
LOAD date,
     12*Sys1/(1+Sys2) as Output
Resident Data;

[PBRM]

THanks!. It was just the tip that I was looking for