Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shaimamahmoud
New Contributor III

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

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,

1 Solution

Accepted Solutions
MVP
MVP

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

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

7 Replies
MVP
MVP

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

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

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

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

shaimamahmoud
New Contributor III

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

This is how the table looks like

Capture.PNG

and these are my dimension and measure

dimension :  status

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

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

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.

shaimamahmoud
New Contributor III

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

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

MVP
MVP

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

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

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

Try the suggestions above using Keepchar