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

[Anonymous]

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.markham‌jagan‌gwassenaar‌stalwar1‌okolyug16‌jontydkpi‌

[sunny_talwar · Accepted solution]

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

];

[Anonymous]

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]

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.

[Anonymous]

Amazing

Thanks for the chance communicating with you