Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people,
I have done a postgres select Query and the resulted data is as follows
|---------------------------------------------------|
Person | Year | Status | Days
|---------------------------------------------------|
SAM | 2016 | free | 39 Days |
Yanny | 2016 | free | 71 Days |
Emy | 2015 | free | 47 Days |
Ibraham| 2015 | free | 30 Days |
SAM | 2016 | busy | 326 Days|
Yanny | 2016 | busy | 294 Days|
Emy | 2015 | busy | 318 Days|
Ibraham|2015 | busy | 335 Days|
|--------------------------------------------------|
I am trying to create atable with a
dimenion : Status
Measure : Sum(<Status>Days)
I am quite sure the syntax is correct, but the summation is giving result ,
it is like I am summing wrong things ( - )
I guess this is becasue of 'XX Days' days that comes from postgres select statement.
please anyone tell me how to overcome this issue.
Thanks,
I guess create Days field in the script using Dual function
LOAD
Dual(Days, KeepChar(Days, '1234567890') * 1) as Days
and then Sum(Days) should work for you
What are you seeing? Wrong numbers or just not seeing anything?
You could use a Preceding Load to adjust your field Days, maybe in this way:
load Person, Year, Status, dual(Days, num(keepchar(Days, '013456789'))) as Days;
SQL Select ...
and your expression could be simply: sum(Days)
- Marcus
This is how the table looks like
and these are my dimension and measure
dimension : status
measure : sum(total<status> (days))
Does your field Days contain the full like "39 Days" or just the muneric of 39 ?
For an arithmetic function to work it needs to be just the numeric value.
Yes, it is like this '39 Days' , and I guess this is the issue why it can't sum the values
I guess create Days field in the script using Dual function
LOAD
Dual(Days, KeepChar(Days, '1234567890') * 1) as Days
and then Sum(Days) should work for you
Try the suggestions above using Keepchar