Posts

Sort a python dictionary

  fruits = { 'apple' : 12 , 'banana' : 9 , 'orange' : 15 , 'mango' : 5 , 'grape' : 20 } ## sort based on <value> fruits_lst = sorted (fruits.items() , key = lambda value : value[ 1 ] , reverse = True ) fruits = {} for key , value in fruits_lst: fruits[key] = value print (fruits) Output : {'grape': 20, 'orange': 15, 'apple': 12, 'banana': 9, 'mango': 5} ## sort based on <key> fruits_lst = sorted (fruits.items() , key = lambda key : key[ 0 ] , reverse = True ) fruits = {} for key , value in fruits_lst: fruits[key] = value print (fruits) {'orange': 15, 'mango': 5, 'grape': 20, 'banana': 9, 'apple': 12}
Post a Comment
Read more

Find Amicable numbers using Python

Amicable numbers   are two different   numbers   related in such a way that the   sum   of the   proper divisors   of each is equal to the other number. The smallest pair of amicable numbers is ( 220 ,  284 ). They are amicable because the proper divisors of 220 are 1, 2, 4, 5, 10, 11, 20, 22, 44, 55 and 110, of which the sum is 284; and the proper divisors of 284 are 1, 2, 4, 71 and 142, of which the sum is 220. (A proper divisor of a number is a positive factor of that number other than the number itself. For example, the proper divisors of 6 are 1, 2, and 3.) Below Python code can be used to identify Amicable numbers from a list. def sumofdivident (num): sum = 0 for i in range ( 1 , num): if num % i == 0 : sum += i return sum def isamicable (num1 , num2): return sumofdivident(num1) == num2 and sumofdivident(num2) == num1 def findpairs (mylist): pairs = [] for i in range ( len (mylist)): f...
1 comment
Read more

Integer division to float division in SQL server

Image
Problem statement : I have a use case where i need to see how much percentage each day with respect to days in a week. Solution in Mathematics :  since there are 7 days in a week , so the solution will be (1/7) * 100 = 14.28 Solution in SQL server: If we apply the same logic in SQL server the result will be 0 because with will be a integer vision. To convert this into float division we need to us 'cast (<integer> as float)' in the denominator. Please refer to the screen shot below.  
Post a Comment
Read more

SCD 2 Using Merge statement in SQL server

Image
 In this example we will look at how to implement SCD2 using merge statement. Target table: Source Table: So if you compare the source and target table , you can see that  * New address for customerid = 1 in the source table , so "New" address needs to be inserted (true) and  "current" address in traget table (customer) needs to be updated as 'false'. * No changes to the address of customer 3 , so no changes. * A "new" customer with customerid = 4 , so that needs to be inserted. Query for SCD 2 : MERGE INTO customers USING (    -- These rows will either UPDATE the current1 addresses of existing customers or INSERT the new addresses of new ----customers   SELECT updates.customerId as mergeKey, updates.*   FROM updates      UNION ALL      -- These rows will INSERT new addresses of existing customers    -- Setting the mergeKey to NULL forces these rows to NOT MATCH and be INSERTed.   SELECT NULL as mergeKe...
2 comments
Read more

Unix Timestamp or Epoch Timestamp to Datetime in SQL Server

Today I was working on extracting files from Amazon S3 to Azure Blob storage and I need to to refer to a control file to determine the start and end time of my extract. But unfortunately the timestamp in the control file was in Unix format.  Select lastSuccessfulWriteTimestamp from from ETL_CONTROL_TABLE ------------------------ 1590796601336 ------------------------ Now i need to convert this Unix Timestamp or Epoch to Datetime format so that my extract job can understand the the actual start date and end date. Now before proceeding let me explain what's a Unix Timestamp or Epoch Timestamp? Unix Timestamp or Epoch Timestamp is the number of  "seconds" from 1st January 1970 00:00:00 hrs. So a datetime  where  Date : 1st January 1970  Time : 03 : 00 : 00 am  will be represented in Unix Timestamp or Epoch Timestamp as   3 (hours) * 60 (min) *  60 (sec) = 10,800  (this is timestamp in seconds) 3 (hours) * 60 (min) *...
Post a Comment
Read more

Flatten / Parsing XML using Azure ADF (Azure data factory)

Image
                           Flatting/Parsing Hierarchical XML using ADF (Azure data factory) Sharing a method of flatting Hierarchical XML using an inbuild method in ADF  ( since its UI based I felt its easier than databricks option)   To flatten “Hierarchical XML” you can use “Flatten” transform in ADF Dataflow.   My source XML structure is mentioned below. To Flatten the structure , we can create a ADF dataflow similar to screen shot mentioned below and use ‘Flatten’ transformation. Steps involved : Step 1 : Create a dataflow in ADF Step 2 : Enable “Data flow debug” option. ·        This will create a cluster in the backend, for the dataflow to run. ·        Cost of this option is priced as 1(hour) x 8 (general purpose cores) x $0.274 = $2.19 ·        This is required only during the development phase ...
2 comments
Read more