Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
shaimamahmoud
Not applicable

Can I run a postgres function to make data transformation before load it to Qlik sense

Hi Dears,

I have some requirement That I want to fulfill, I see it a little bit complex

I have this first table in postgres DB and I want to transform it into the second table

First

========================================

Task   |  Person      |            Start       |            End            |

-------------------------------------------------------------------------

task 1 |         X        |  1-feb-2015        | 15-feb-2015         |

task 2 |         Y        |  1-feb-2015        | 5-mar-2015          |

task 3 |         A        |  3-Oct-2015       | 15-feb-2016         |

task 4 |         G        |  1-apr-2015        | 5-may-2015         |

task 5 |          Z       |  10-apr-2015       | 15-apr-2015         |

task 6 |         Y        |  1-Jan-2015        | 5-Aug-2016          |

task 7 |         X        |  11-Jul-2015        | 22-Dec-2017       |

========================================

I want to transform this data into the following structure


Second

========================================

Task   |  Person      |    Days     |     Month     |    Year      |

-------------------------------------------------------------------------

task 1 |         X        |  15 Days   |    feb        |   2015       |

task 2 |         Y        |  28 Days   |    feb        |   2015       |

task 2 |         Y        |   5 Days    |    mar       |   2015      |

task 3 |         A        |  27 Days   |    oct         |   2015      |

task 3 |         A        |  30 Days   |    nov        |   2015      |

task 3 |         A        |  30 Days   |    dec        |   2015      |

task 3 |         A        |  30 Days   |    jan         |   2016      |

task 3 |         A        |  15 Days   |    feb         |   2016      |


task 4 |         G        |   30 Days  |    apr         |  2015      |

task 4 |         G        |   5 Days    |    may       |   2015     |


task 5 |          Z       |   5 Days    |    apr         |   2015     |

task 6 |         Y        |   30 Days  |     jun        |    2016   |

task 6 |         Y        |   30 Days  |    Jul         |    2016   |

task 6 |         Y        |    5 Days   |   Aug        |   2016    |

task 7 |         X        |    19 Days |    Jul         |    2015   |

.....

task 7 |         X        |   22 Days  |    Dec       |     2015   |

task 7 |         X        |   30 Days  |    Jan      |   2016     |

...

task 7 |         X        |   30 Days   |    Dec     |     2016   |

task 7 |         X        |   30 Days   |    Jan      |     2017   |

...

task 7 |         X        |   22 Days   |    Dec     |     2017   |

======================================



I know on DB level , this can be done easily using a function, but how can I do this inside Qlik sense?????????

first Question, can I use a function to do it in postgres and then call this function somehow in my Qlik Sense app to load the data ?

Second Question, If I can't do it using a postgres function , can I do it using a Query ?

bill.markhamjagangwassenaarstalwar1okolyug16jontydkpi

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Can I run a postgres function to make data transformation before load it to Qlik sense

Can you check if this meets your requirement?

Table:

LOAD Task,

  Person,

    If(IterNo() = 1,

    RangeMin(End, Floor(MonthEnd(Start))) - Start,

    RangeMin(End, Floor(MonthEnd(Start, IterNo() - 1))) - MonthStart(Start, IterNo() - 1)) + 1 as Days,

    Month(MonthStart(Start, IterNo() - 1)) as Month,

    Year(MonthStart(Start, IterNo() - 1)) as Year

While IterNo() <= MonthDiff; 

LOAD *,

  ((Year(End)*12) + Month(End)) - (((Year(Start)*12) + Month(Start))) + 1 as MonthDiff;

LOAD * INLINE [

Task, Person, Start, End

task 1, X, 1-Feb-2015, 15-Feb-2015

task 2, Y, 1-Feb-2015, 5-Mar-2015

task 3, A, 3-Oct-2015, 15-Feb-2016

task 4, G, 1-Apr-2015, 5-May-2015

task 5, Z, 10-Apr-2015, 15-Apr-2015

task 6, Y, 1-Jan-2015, 5-Aug-2016

task 7, X, 11-Jul-2015, 22-Dec-2017

];

4 Replies
sunny_talwar
Not applicable

Re: Can I run a postgres function to make data transformation before load it to Qlik sense

Can you check if this meets your requirement?

Table:

LOAD Task,

  Person,

    If(IterNo() = 1,

    RangeMin(End, Floor(MonthEnd(Start))) - Start,

    RangeMin(End, Floor(MonthEnd(Start, IterNo() - 1))) - MonthStart(Start, IterNo() - 1)) + 1 as Days,

    Month(MonthStart(Start, IterNo() - 1)) as Month,

    Year(MonthStart(Start, IterNo() - 1)) as Year

While IterNo() <= MonthDiff; 

LOAD *,

  ((Year(End)*12) + Month(End)) - (((Year(Start)*12) + Month(Start))) + 1 as MonthDiff;

LOAD * INLINE [

Task, Person, Start, End

task 1, X, 1-Feb-2015, 15-Feb-2015

task 2, Y, 1-Feb-2015, 5-Mar-2015

task 3, A, 3-Oct-2015, 15-Feb-2016

task 4, G, 1-Apr-2015, 5-May-2015

task 5, Z, 10-Apr-2015, 15-Apr-2015

task 6, Y, 1-Jan-2015, 5-Aug-2016

task 7, X, 11-Jul-2015, 22-Dec-2017

];

shaimamahmoud
Not applicable

Re: Can I run a postgres function to make data transformation before load it to Qlik sense

I just want to give you a very very big Thanks, that you aunderstood my Question also , you solved it very simply,

I have tried to solve it also but using a native SQL.

And here comes a very important Question that I want to ask an expert like you,

Generally, I know that I can do any transformation on data using Qlik sense,

I can Also do this using SQL Queries

Is it better to load the data and make its transformation in the native SQL or to make it in the qlik sense????

sunny_talwar
Not applicable

Re: Can I run a postgres function to make data transformation before load it to Qlik sense

As a general rule of thumb of what I do: I try to bring the least number of records from SQL. So in this case, I would probably do the transformation in QlikView/Qlik Sense, unless you SQL engine is way powerful then your QlikView/Qlik Sense engine.

In another case where I have to use a where statement to bring only certain type of records, I would try to implement it in the SQL just to make sure that the number of records brought over are kept to minimum.

shaimamahmoud
Not applicable

Re: Can I run a postgres function to make data transformation before load it to Qlik sense

Amazing

images.jpg

Thanks for the chance communicating with you