Showing posts with label Function. Show all posts
Showing posts with label Function. Show all posts

Wednesday, 2 January 2019

How to iterate MongoDB Cursor in Python - Intermediate I


Whenever querying on mongodb, always store the output into a variable, called cursor, before performing any operation on data. It will keep your data into variable without messing your output ground. PyMongo Cursor variable supports few functions which helps with few information without actual seeing your data such as retrieved data count or distinct values in particular key etc.

In this session, we will learn about the mongo db cursor variables, for this exercise also we are going to use 'USER' database hosted on free tier MongoDB Atlas (M0) Server.


Jupyter Notebook can be accessed HERE also

= =


Next Post on this Series and more on MongoDB can be find here -> LINK





Like the below page to get the update  
Facebook Page      Facebook Group      Twitter Feed      Google+ Feed      Telegram Group     


Friday, 11 December 2015

DataStage Scenario #11 - Get numeric or alphabets only



Goal - Extract numeric part and alpha part from a string as below

Input:

Source
ATUL1234
SINGH374
I23S
C343LEAR





Output -

Source Part1  Part2
ATUL1234 ATUL 1234
SINGH374 SINGH 374
I23S IS 23
C343LEAR CLEAR 343








Like the below page to get update  



Friday, 30 October 2015

UpCase and DownCase String Function : DataStage Transformation


Downcase(column):
                  This function converts all the input character to LOWER case where input can be in any case.

input = 'This is me'
input1 = 'this is me'
input2 = 'THIS IS ME'

DownCase(input)    =  'this is me'
DownCase(input1)   =  'this is me'
DownCase(input2)   =  'this is me'


Upcase(column): 
                  This function converts all the input character to UPPER case where input can be in any case.

input = 'This is upper case function'
input1 = 'this is me'
input2 = 'THIS IS ME'

UpCase(input)    =  'THIS IS UPPER CASE FUNCTION'
UpCase(input1)   =  'THIS IS ME'
UpCase(input2)   =  'THIS IS ME'





Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://groups.google.com/forum/#!forum/datagenx

Wednesday, 28 October 2015

Left and Right String Function - DataStage Transformation



Today, we will discuss two more datastage transformer functions - Left & Right.  These two functions use to get or cut the string from the input data, Let's have a look -


Left(input, number)  
input : - input column
number :- the no of characters you want to fetch from Left most of string


input = 'This is a test of fuctions."

Left(input,9)  =    'This is a'
Left(input, 3) =    'Thi'

Right(input, number)
input : - input column
number :- the no of characters you want to fetch from Right most of string


input = 'This is a test of fuctions."

Right(input,9)  =    'fuctions.'
Right(input, 14) =    't of fuctions.'


Use of Left/Right function to get SubString -

These functions used to get substring from an input if the start and end character location known or can be derived. Let's see an example -

we need to cut input from 2nd to 6th character

input = 'This is a test of fuctions."

left(input, 6) = 'This i'           #left function cut the left 6 character
Right(left(input,5),5) = 'his i'    #Use right func, to cut right 6-2+1 = 5 char 






Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://groups.google.com/forum/#!forum/datagenx

Monday, 21 September 2015

DataStage Scenario #5 - Oracle Rank & Rownum



Scenario:

a. Design a job where we read the data from squential file.
b. Analyze the data into job and generate these 3 columns



Example Input:

Data
Gama
Charlie
Alpha
Beta
Alpha
Charlie
Delta
Alpha

Output Expected:

Data  ROW_NUMBER   RANK   DENSE_RANK 
Alpha            1      1            1
Alpha            2      1            1
Alpha            3      1            1
Beta            4      4            2
Charlie            5      5            3
Charlie            6      5            3
Delta            7      7            4
Gama            8      8            5



Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://groups.google.com/forum/#!forum/datagenx

Friday, 28 August 2015

Datastage Transformer String Functions - Index





Index(String, Substring, Occurrence):   
   
It will returns starting character position of substring. We can use it as a 'grep' command of Linux when we have to check if given character or substring is existed in input string or not. 
        
Ex:-
1. Suppose, we have to check whether substring 'le' is existing in input column 'Incol' or not?

Transformation derivation  -
If Index(Incol, 'le', 1) > 0 then 'Exist' Else 'Not Exists'


2. Let's assume we are getting 'India' as a input string, what will be the output of below derivations?
     
Transformation derivation  -
Index('India', 'a', 1)         =  5
Index('India', 'i', 1)         =   4   # this will not give 1 as matching is case sensitive
Index('India', 'nd', 1)      =   2




Saturday, 22 August 2015

VLookup in MS Excel





We all agree on this, we have to use MS Excel in our day to day work whether it is technical or not. A day before I came across a Excel Function which is same as DS Lookup :-) called VLookUp. I loved this function cause this reduce my work of 30 min to few seconds. So just want to share this function here, by the way there are lot of material you can find on net for this but this is my version ---


What was my task ? 
I have to pickup a hyperlink value from another sheet by matching a doc name.
If Sheet1.Doc = Sheet2.Doc Then HyperLink Else ''

My Excel sheets were look like --




The syntax of the VLOOKUP function is:
=VLOOKUP(lookup value, table range, output column index , [true/false])

Lookup Value        --- First Key column ( A1 )
Table Range         --- Lookup Table Range ( Sheet2!$A$2:$B$15 )
Output column index --- output column number ( for my case 2 (col B))
true/false          --- True means that an approximate match is acceptable, and False means that only an exact match is acceptable.

Table Range can be defined like below --
Lookup_Sheet_Name ! First_Row_First_Col : Last_Row_Last_Col 

Suppose , your sheet name is - Sheet2
First_Row_First_Col - A2    --> $A$2
Last_Row_Last_Col  - B15  --> $B$15

Table Range will be -- Sheet2!$A$2:$B$15

So,  Our VLOOKUP function will be - 
=VLOOKUP(A1, Sheet2!$A$2:$B$15, 2 , true)

Place this function to that cell where you want to populate the value from lookup table, this will work like - 
If A1 = ( table range) then pick sheet 2 column no 2 Else ''