Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys,
I'm trying to clean up my script and reduce the synthetic keys. I hope someone can help me.
My question is: Can renamed field be used within a key? As in:
LOAD
datum & '|' & UPPER(klant) & '|' & module & '|' & profiel& '|' & Year(datum) & '|' & Month(datum) & '|' & if(len(datum)>0, 'Q' & ceil(month(datum)/3)) As Quarter as key,
FROM
How do you go by renaming a field and using it in within a key? Is it even possible? What are possible alternative sollutions?
And ofcourse how does it, if it does work with if statements within keys?
Thanks in advance.
Greetings,
Robert
You can alternatively use a preceding LOAD to reuse fields defined in the following LOAD, like
LOAD *,
datum & '|' & UPPER(klant) & '|' & module & '|' & profiel& '|' & year & '|' & month & '|' & Quarter as key;
LOAD
datum,
klant,
module,
profiel,
Year(datum) as year,
Month(datum) as month,
if(len(datum)>0, 'Q' & ceil(month(datum)/3)) As Quarter
FROM
You could use it the way below
LOAD
datum & '|' & UPPER(klant) & '|' & module & '|' & profiel& '|' & Year(datum) & '|' & Month(datum) & '|' & if(len(datum)>0, 'Q' & ceil(month(datum)/3)) as key
hth
Sasi
You can alternatively use a preceding LOAD to reuse fields defined in the following LOAD, like
LOAD *,
datum & '|' & UPPER(klant) & '|' & module & '|' & profiel& '|' & year & '|' & month & '|' & Quarter as key;
LOAD
datum,
klant,
module,
profiel,
Year(datum) as year,
Month(datum) as month,
if(len(datum)>0, 'Q' & ceil(month(datum)/3)) As Quarter
FROM
Hi,
Is this field neccessary
Year(datum) & '|' & Month(datum) & '|' & if(len(datum)>0, 'Q' & ceil(month(datum)/3)) As Quarter
in your key
Regards
Hi Max,
No, actually it is not neccessary. But then I would get my quarters in synthetic keys.
The relation between the tables is neccessary though, linking the quarters together.
Because I'm working with two types of users and the quarter filter should influence them both.
By the way guys, thanks for all the replies.
By the time it took to load my script there where two usable sollutions.
Thanks again.
Robert
Hi,
What if we rename those field in either of the table.
Regards
Hi Max,
Yes, they used to be named differently, but would it still be possible to use them in a filter.
As in Q1-Q4 and having the filter influence both the tables?
Hi,
Yes You can use of it as filter and it will give you correct result because Year,Month and Quater fields are creating from datum field which is part of your key.
Therefore, I think you dont required below field as part of your key.
Year(datum) & '|' & Month(datum) & '|' & if(len(datum)>0, 'Q' & ceil(month(datum)/3)) As Quarter
Other wise what Swuehl suggest is correct.
Regards
Ok, thanks.. I expected as such, but then I would have syn-keys allover, so I decided to clean it up a little.
I will take that into considaration and will try several sollutions in search of the best.
Thanks Max..