Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sibrulotte
Contributor 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
Valued Contributor III

Re: Joining the sum of a colomn as a field value

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;

3 Replies
sibrulotte
Contributor III

Re: Joining the sum of a colomn as a field value

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
Valued Contributor III

Re: Joining the sum of a colomn as a field value

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
Contributor III

Re: Joining the sum of a colomn as a field value

Yeap, that does it.

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

Thanks

Simon

Community Browser