Trying to Sum(intervalss) coming from Postgres DB select Statement

[Anonymous]

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,

[sunny_talwar]

What are you seeing? Wrong numbers or just not seeing anything?

[marcus_sommer]

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

[Anonymous]

This is how the table looks like

and these are my dimension and measure

dimension :  status

measure   :  sum(total<status> (days))

[Anonymous]

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.

[Anonymous]

Yes, it is like this '39 Days' , and I guess this is the issue why it can't sum the values

[sunny_talwar · Accepted solution]

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

[Anonymous]

Try the suggestions above using Keepchar