Qlik Community

New to Qlik Sense

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shaimamahmoud
Creator
Creator

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

];

shaimamahmoud
Creator
Creator
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.

shaimamahmoud
Creator
Creator
Author

Amazing

images.jpg

Thanks for the chance communicating with you