Forum Discussion

vbetharasi's avatar
vbetharasi
New Contributor
6 years ago

PostGreSql connectivity

Hi, While trying to connect PostgreSQL using JDBC getting below error. . Could you please help to resolve this issue. Thanks in advance! Thanks, Venu
del's avatar
del
Contributor III
4 years ago

@Majid I’m glad the above helped.

I think I may be derailing too much by suggesting the expression library. It was a creative thought, but would require readjusting downstream logic to reach desired end result.

But, for an exercise, I put this together to show where my thought was leaning.
It basically changes your source:

	{
		"UBER_ID": [
			"1",
			"Integer_pattern"
		],
		"First_name": [
			"Majid",
			"TextOnly_pattern"
		],
		"Last_name": [
			"",
			"TextOnly_pattern"
		]
	}

to this:

	{
		"UBER_ID": [
			"1",
			"Integer_pattern",
			"valid"
		],
		"First_name": [
			"Majid",
			"TextOnly_pattern",
			"valid"
		],
		"Last_name": [
			"",
			"TextOnly_pattern",
			"invalid"
		]
	}

But you’d still have to handle this creatively in downstream snaps to get your desired results.

Community.10510.v3_2021_08_06.slp (4.9 KB)
community10510v3.expr.txt (376 Bytes)

    • vaidyarm's avatar
      vaidyarm
      Contributor
      4 years ago

      Have no words to thank you for this, I always thought to do such things via python script, but the core snaplogic solution you gave is very well thought !!

      Thanks a lot !!

    • vaidyarm's avatar
      vaidyarm
      Contributor
      4 years ago

      Thanks @tlikarish ,

      This one looks sleek but the problem is that I can have more than the “ID” column in the source, and since you have hardcoded the fields, it would be difficult to incorporate that.
      but this one will be a good one when we have a fixed list of fields coming from the source

      Thanks

    • mohit_jain's avatar
      mohit_jain
      New Contributor III
      2 years ago

      Hi  tlikarish 
      I saw your pipeline, its perfect but I have one more requirement.
      If I want to spit the date by quarterly can you please let me know where we need to change the pipeline for it.
      For examp:
      Input:

      We just need the start_date and end_date by quarterly. 

       

      THANKS IN ADVANCE.

       

  • j_angelevski's avatar
    j_angelevski
    Contributor III
    4 years ago

    Hi @vaidyarm,

    You can also do this with a single expression in a Mapper. It is a little bit complex expression but I think it does the job.

    Date.parse($EndDate).getFullYear() - Date.parse($StartDate).getFullYear() > 0 ? sl.range(0, Date.parse($EndDate).getFullYear() - Date.parse($StartDate).getFullYear() + 1).map((val, i, arr) => $.extend({"StartDate": val == 0 ? Date.parse($StartDate).toLocaleDateTimeString({"format": "yyyy-MM-dd"}) : Date.parse($StartDate).withDayOfYear(1).plusYears(val).toLocaleDateTimeString({"format": "yyyy-MM-dd"}), "EndDate": val == arr.length - 1 ? Date.parse($EndDate).toLocaleDateTimeString({"format": "yyyy-MM-dd"}) : Date.parse($StartDate).withMonthOfYear(12).withDayOfMonth(31).plusYears(val).toLocaleDateTimeString({"format": "yyyy-MM-dd"})})) : sl.ensureArray($)

    This will work regardless of how many fields the input data has as long as there is “StartDate” and “EndDate” in the input data.

    Generat_Dates_2021_09_29.slp (5.0 KB)

  • vaidyarm's avatar
    vaidyarm
    Contributor
    4 years ago

    All of the above solutions worked, I was wondering if we could take this one step forwards with date segregation as below
    (earlier it was as per end of the year, now its one year cycle, and also the diff between the start and end date should not be greater than 365/366 days considering leap years if possible )

    Input :

    ID	StartDate	EndDate
1	2016-05-12	2019-11-30

    Output :

    ID StartDate EndDate
1 2016-05-12 2016-11-30
1 2016-11-31 2017-11-30
1 2017-11-31 2018-11-30
1 2018-11-31 2019-11-30

    this is how the contract management system would also be implemented.

    • vaidyarm's avatar
      vaidyarm
      Contributor
      4 years ago

      I just tried to build on top of your logic to create the above scenario, was able to do it, refer to the attached pipeline, although design-wise it may not seem that cleaner and concise but it serves the purpose and handles the leap year exception as well
      Input:

      Output:

      It was helpful for contract management problems we have come across so far.

      Contract_Date_splitter_2021_10_11.slp (23.2 KB)