Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
shaimamahmoud
Creator
Creator

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
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Creator
Creator
Author

This is how the table looks like

Capture.PNG

and these are my dimension and measure

dimension :  status

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

bill_markham
Champion III
Champion III

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
Creator
Creator
Author

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

sunny_talwar

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

bill_markham
Champion III
Champion III

Try the suggestions above using Keepchar