Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sibrulotte
Creator III
Creator III

Joining the sum of a colomn as a field value

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?

1 Solution

Accepted Solutions
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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;

View solution in original post

3 Replies
sibrulotte
Creator III
Creator III
Author

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
Partner - Specialist III
Partner - Specialist III

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
Creator III
Creator III
Author

Yeap, that does it.

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

Thanks

Simon