- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
(
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?
- Tags:
- qlikview_scripting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Well 10 minutes later, I succeeded, sort of:
LOAD Règle,
Description
FROM
(
Titre:
LOAD Titre,
[Flag 1],
[Flag 2],
[Flag 3]
FROM
(
//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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yeap, that does it.
A bit teadeous all of this in the end, but definitly a win.
Thanks
Simon