4 Replies Latest reply: Aug 18, 2016 6:33 AM by Shaima Mahmoud RSS

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

    Shaima Mahmoud

      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