Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
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

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

];

View solution in original post

4 Replies
sunny_talwar

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
Not applicable
Author

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
Not applicable
Author

Amazing

images.jpg

Thanks for the chance communicating with you