Joining the sum of a colomn as a field value

[sibrulotte]

Hi,

I have two tables (see excel file), one with Rules (Règles sheet) and one with Titles (Titre sheet).

I'm trying to sum the value of Flag 1 for each Titre, and put that value in a single field value for a new filed called Bris.

Here was a script idea that I thought would work:

Règles:

LOAD Règle,
     Description
FROM
[CONFORM.xlsx]
(ooxml, embedded labels, table is Regles);

Titre:
LOAD Titre,
     [Flag 1],
     [Flag 2],
     [Flag 3]
FROM

(ooxml, embedded labels, table is Titres);


left join (Règles)
load
sum([Flag 1]) as Bris,
'1' as Règle
resident Titre;



left join (Règles)
load
sum([Flag 2]) as Bris,
'2' as Règle
resident Titre;


left join (Règles)
load
sum([Flag 3]) as Bris,
'3' as Règle
resident Titre;

Evidently this doesn't work.

I could cald my colomne names Bris1, Bris2, Bris3 and that would bring me back what I need, but this summ needs to show up in a single field called Bris.

Help?

[sibrulotte]

Well 10 minutes later, I succeeded, sort of:

LOAD Règle,

     Description

FROM



(ooxml, embedded labels, table is Regles);





Titre:

LOAD Titre,

     [Flag 1],

     [Flag 2],

     [Flag 3]

FROM



(ooxml, embedded labels, table is Titres);





//left join (Règles)

Bris:

load

sum([Flag 1]) as Bris,

'1' as Règle

resident Titre;



//left join (Règles)

load

sum([Flag 2]) as Bris,

'2' as Règle

resident Titre;



//left join (Règles)

load

sum([Flag 3]) as Bris,

'3' as Règle

resident Titre;

I'm stuck with an extra table, and maybe I'll try to use it as a mapping table. Don't knwo how yet. I'll figure things out.

Thank you for those who thought about responding

[marcus_malinow · Accepted solution]

Hi Simon,

You're nearly there I think but it sounds like you might want to add something like this.

At the end of your script:

LEFT JOIN (Regles)

LOAD Regle,

    Bris

RESIDENT Bris;

DROP TABLE Bris;

[sibrulotte]

Yeap, that does it.

A bit teadeous all of this in the end, but definitly a win.

Thanks

Simon