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     

Read More

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  

https://www.facebook.com/datastage4you

https://twitter.com/datagenx

https://plus.google.com/+AtulSingh0/posts

https://groups.google.com/forum/#!forum/datagenx

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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 ''

Read More