MSBI - Business Intelligence

host

2024-09-03T09:58:00.122-07:00

DECLARE @InsertStatement NVARCHAR(MAX);

SELECT @InsertStatement =

'INSERT INTO SalesEmployees (Id, Name, Age) ' +

'SELECT ' +

STRING_AGG('''' + CAST(Id AS NVARCHAR(MAX)) + '''', ', ') +

' FROM Employees WHERE Department = ''Sales'';'

FROM Employees

WHERE Department = 'Sales';

PRINT @InsertStatement;

requirement.txt

2024-08-28T20:08:00.000-07:00

import os

# Path to the directory containing the downloaded packages

package_dir = '/path/to/my_packages'

# List to hold the package names and versions

requirements = []

# Iterate over all files in the directory

for filename in os.listdir(package_dir):

# Process .whl files

if filename.endswith('.whl'):

# Split the filename by dashes and underscores to extract the package name and version

parts = filename.split('-')

package_name = parts[0]

version = parts[1]

requirements.append(f'{package_name}=={version}')

# Process .tar.gz files

elif filename.endswith('.tar.gz'):

# Remove the .tar.gz and split the filename by dashes to extract the package name and version

parts = filename.replace('.tar.gz', '').split('-')

package_name = parts[0]

version = parts[1]

requirements.append(f'{package_name}=={version}')

# Write the requirements to a requirements.txt file

with open('requirements.txt', 'w') as f:

for requirement in requirements:

f.write(requirement + '\n')

print("requirements.txt created successfully.")

Optimizing Tableau Dashboard Performance in Web Applications: Strategies for Caching and Faster Load Times"

2024-08-28T09:43:00.000-07:00

Caching a Tableau dashboard that has been rendered within an iframe is difficult, but here are some suggestions you can look into to make things better and seem like the dashboard is cached:

Browser Caching
Make sure the server is configured to cache Tableau dashboard resources by using appropriate cache headers for the HTML, CSS, JS, and image resources that the dashboard uses. The browser will cache all those resources after the first load, thus making load times shorter for revisits.
Via a Reverse Proxy
You can configure a reverse proxy in front of your Tableau server, for example, Nginx or Apache. This reverse proxy can cache the dashboard resources; thus, on subsequent hits for the same dashboard, it serves that copy from the cache without going again to Tableau for the data. This setup drastically improves load times on successive visits.
Loading the Dashboard
You could preload the Tableau dashboard in the background of the application flow before the user even navigates to the page that contains the iframe. Thus, when the user visits the page, the dashboard will actually have been already loaded in the background, giving an idea of a cached dashboard. One could create a hidden iframe, for example using display: none; in CSS, on an earlier page or loaded asynchronously.
Embed Tableau with JavaScript API
Instead of using the direct iframe, one can use Tableau's JavaScript API to embed the dashboard, thus gaining more control over the loading process and behavior regarding cache. In such a way, you could either implement logic to load the dashboard in the background or control how and when the dashboard is refreshed.
Session Persistence
In case any application allows it, you may store a state of a Tableau dashboard into a session (or local storage) upon first load. This stored state is used to reload the dashboard any time the user visits the page.
Tab Switching Preload
In this scenario, if the dashboard resides inside a tabbed interface, one can trigger the iframe to load the Tableau dashboard behind the scenes at the time the tabbed interface is loaded—before the user has actually moved to that tab.

The application of each of these can be used for the decrease in perceived loading time for your Tableau dashboard on subsequent visits, making for a really smooth experience. The best technique would depend on the specific setup of your web application and the environment of the server.

T

2024-08-28T07:57:00.000-07:00

# List of allowed fund names

keep_fund_names = ["A", "B", "C", "D"]

# Filter the funds to include only the allowed names

keep_fund_names = {name: id for name, id in fund_names.items() if name in keep_fund_names}

Steps to create SSH key from git bash

2024-08-07T02:43:00.000-07:00

Generating an SSH key in OpenSSH involves a few simple steps. Here’s how you can do it:

1. Open a Terminal

If you're on Linux or macOS, you can use the built-in terminal.
On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed.

2. Generate the SSH Key Pair

Run the following command:

bash
ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

Here’s what each option means:
- -t rsa: Specifies the type of key to create, in this case, RSA.
- -b 4096: Specifies the number of bits in the key, 4096 bits is a good choice for strong encryption.
- -C "your_email@example.com": Adds a label to the key, usually your email address.

3. Choose a File Location

After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in `~/.ssh/id_rsa` (on Linux or macOS) or in `C:\Users\YourUserName\.ssh\id_rsa` (on Windows).
Press `Enter` to accept the default location, or specify a different file path.

4. Set a Passphrase (Optional)

You will be prompted to enter a passphrase. This is an additional layer of security; you can leave it blank if you don't want to use a passphrase, but using one is recommended.

5. Add the SSH Key to the SSH Agent

Start the SSH agent in the background:
```
bash
eval "$(ssh-agent -s)"
```
Add your SSH private key to the agent:
```
bash
ssh-add ~/.ssh/id_rsa
```

6. Copy the SSH Public Key

To add your SSH key to a service like GitHub, GitLab, or a server, you'll need to copy the public key:
```
bash
cat ~/.ssh/id_rsa.pub
```
Copy the output of this command, which starts with ssh-rsa, and add it to the desired service.

7. Testing the SSH Connection

You can test the SSH connection to ensure that everything is working correctly. For example, to test GitHub:
```
bash
ssh -T git@github.com
```
If everything is set up correctly, you should see a message indicating that you’ve successfully authenticated.

This process will generate a secure SSH key pair that you can use for authentication with servers, version control systems, and other services.

p

2024-08-05T10:41:00.000-07:00

import requests

import json

from requests.auth import HTTPBasicAuth

# Define the GraphQL endpoint

url = 'https://graphql-demo.mead.io/'

# Define user ID and password for authentication

user_id = 'your_user_id'

password = 'your_password'

# Define the GraphQL query with date range filter

query = """

{

events(filter: { startDate: { gte: "2023-01-01" }, endDate: { lte: "2023-01-31" } }) {

name

date

location

}

"""

# Perform the query with authentication

response = requests.post(url, json={'query': query}, auth=HTTPBasicAuth(user_id, password))

data = response.json()

# Print the result

print(json.dumps(data, indent=2))

Last 30 days

2024-08-05T08:57:00.000-07:00

SELECT

MIN(CalendarDate) AS StartDate

FROM (

SELECT

TOP 30

DATEADD(DAY, -n, CAST(GETDATE() AS DATE)) AS CalendarDate

FROM (

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n

FROM

sys.objects

) AS Numbers

WHERE

DATEPART(WEEKDAY, DATEADD(DAY, -n, CAST(GETDATE() AS DATE))) NOT IN (1, 7) -- 1 = Sunday, 7 = Saturday

ORDER BY

CalendarDate DESC

) AS Last30WorkingDays;

Formula

2024-07-26T08:45:00.000-07:00

WITH DateSequence AS (

SELECT CAST(GETDATE() AS DATE) AS BusinessDate

UNION ALL

SELECT DATEADD(DAY, -1, BusinessDate)

FROM DateSequence

WHERE DATEADD(DAY, -1, BusinessDate) > DATEADD(DAY, -14, GETDATE()) -- Generate enough days to cover weekends

BusinessDays AS (

SELECT BusinessDate

FROM DateSequence

WHERE DATENAME(WEEKDAY, BusinessDate) NOT IN ('Saturday', 'Sunday')

)

SELECT MIN(BusinessDate) AS TMinus5BusinessDay

FROM (

SELECT BusinessDate, ROW_NUMBER() OVER (ORDER BY BusinessDate DESC) AS RowNum

FROM BusinessDays

) AS NumberedBusinessDays

WHERE RowNum = 5

OPTION (MAXRECURSION 100);

PROCEDURE GetTopResults

2024-07-24T21:32:00.000-07:00

CREATE PROCEDURE GetTopOrBottomResults

@N INT, -- Accepts number of top/bottom results

@OrderType VARCHAR(10) -- Accepts 'Top', 'Bottom', or 'All'

BEGIN

SET NOCOUNT ON;

-- Determine the number of rows in the table if 'All' is specified

DECLARE @TotalRows INT;

SET @TotalRows = (SELECT COUNT(*) FROM YourTable);

-- Handle the 'All' case by setting @N to the total number of rows

IF @OrderType = 'All'

BEGIN

SET @N = @TotalRows;

END

-- Determine the query to execute based on the @OrderType parameter

IF @OrderType = 'Top' OR @OrderType = 'All'

BEGIN

-- Select top N results ordered by SomeColumn

SELECT TOP (@N) *

FROM YourTable

ORDER BY SomeColumn; -- Adjust the ordering column as needed

END

ELSE IF @OrderType = 'Bottom'

BEGIN

-- Select bottom N results ordered by SomeColumn in descending order

;WITH CTE AS (

SELECT *, ROW_NUMBER() OVER (ORDER BY SomeColumn DESC) AS RowNum

FROM YourTable

)

SELECT *

FROM CTE

WHERE RowNum <= @N

ORDER BY SomeColumn; -- Adjust the ordering column as needed

END

ELSE

BEGIN

-- If invalid @OrderType is provided, return an error message

RAISERROR('Invalid OrderType. Please specify "Top", "Bottom", or "All".', 16, 1);

END

END;

SUBSTRING(rank_name, 1, CHARINDEX(' ', rank_name) - 1) AS FirstWord, SUBSTRING(rank_name, CHARINDEX(' ', rank_name) + 1, LEN(rank_name) - CHARINDEX(' ', rank_name)) AS SecondWord

CurrencyReference Table

2024-07-04T06:34:00.000-07:00

-- Create the CurrencyReference table

CREATE TABLE CurrencyReference (

CurrencyDescription NVARCHAR(100),

CurrencyShortName CHAR(3)

);

-- Insert all possible currency descriptions and their corresponding ISO codes

INSERT INTO CurrencyReference (CurrencyDescription, CurrencyShortName) VALUES

('Afghan Afghani', 'AFN'),

('Albanian Lek', 'ALL'),

('Algerian Dinar', 'DZD'),

('Angolan Kwanza', 'AOA'),

('Argentine Peso', 'ARS'),

('Armenian Dram', 'AMD'),

('Aruban Florin', 'AWG'),

('Australian Dollar', 'AUD'),

('Azerbaijani Manat', 'AZN'),

('Bahamian Dollar', 'BSD'),

('Bahraini Dinar', 'BHD'),

('Bangladeshi Taka', 'BDT'),

('Barbadian Dollar', 'BBD'),

('Belarusian Ruble', 'BYN'),

('Belize Dollar', 'BZD'),

('Bermudian Dollar', 'BMD'),

('Bhutanese Ngultrum', 'BTN'),

('Bolivian Boliviano', 'BOB'),

('Bosnia-Herzegovina Convertible Mark', 'BAM'),

('Botswana Pula', 'BWP'),

('Brazilian Real', 'BRL'),

('British Pound Sterling', 'GBP'),

('Brunei Dollar', 'BND'),

('Bulgarian Lev', 'BGN'),

('Burundian Franc', 'BIF'),

('Cabo Verdean Escudo', 'CVE'),

('Cambodian Riel', 'KHR'),

('Canadian Dollar', 'CAD'),

('Cayman Islands Dollar', 'KYD'),

('Central African CFA Franc', 'XAF'),

('Chilean Peso', 'CLP'),

('Chinese Yuan', 'CNY'),

('Colombian Peso', 'COP'),

('Comorian Franc', 'KMF'),

('Congolese Franc', 'CDF'),

('Costa Rican Colón', 'CRC'),

('Croatian Kuna', 'HRK'),

('Cuban Peso', 'CUP'),

('Czech Koruna', 'CZK'),

('Danish Krone', 'DKK'),

('Djiboutian Franc', 'DJF'),

('Dominican Peso', 'DOP'),

('East Caribbean Dollar', 'XCD'),

('Egyptian Pound', 'EGP'),

('Eritrean Nakfa', 'ERN'),

('Estonian Kroon', 'EEK'),

('Ethiopian Birr', 'ETB'),

('Euro', 'EUR'),

('Fijian Dollar', 'FJD'),

('Gambian Dalasi', 'GMD'),

('Georgian Lari', 'GEL'),

('Ghanaian Cedi', 'GHS'),

('Gibraltar Pound', 'GIP'),

('Guatemalan Quetzal', 'GTQ'),

('Guinean Franc', 'GNF'),

('Guyanese Dollar', 'GYD'),

('Haitian Gourde', 'HTG'),

('Honduran Lempira', 'HNL'),

('Hong Kong Dollar', 'HKD'),

('Hungarian Forint', 'HUF'),

('Icelandic Króna', 'ISK'),

('Indian Rupee', 'INR'),

('Indonesian Rupiah', 'IDR'),

('Iranian Rial', 'IRR'),

('Iraqi Dinar', 'IQD'),

('Israeli New Shekel', 'ILS'),

('Jamaican Dollar', 'JMD'),

('Japanese Yen', 'JPY'),

('Jordanian Dinar', 'JOD'),

('Kazakhstani Tenge', 'KZT'),

('Kenyan Shilling', 'KES'),

('Kuwaiti Dinar', 'KWD'),

('Kyrgyzstani Som', 'KGS'),

('Lao Kip', 'LAK'),

('Latvian Lats', 'LVL'),

('Lebanese Pound', 'LBP'),

('Lesotho Loti', 'LSL'),

('Liberian Dollar', 'LRD'),

('Libyan Dinar', 'LYD'),

('Lithuanian Litas', 'LTL'),

('Macanese Pataca', 'MOP'),

('Macedonian Denar', 'MKD'),

('Malagasy Ariary', 'MGA'),

('Malawian Kwacha', 'MWK'),

('Malaysian Ringgit', 'MYR'),

('Maldivian Rufiyaa', 'MVR'),

('Mauritanian Ouguiya', 'MRU'),

('Mauritian Rupee', 'MUR'),

('Mexican Peso', 'MXN'),

('Moldovan Leu', 'MDL'),

('Mongolian Tögrög', 'MNT'),

('Moroccan Dirham', 'MAD'),

('Mozambican Metical', 'MZN'),

('Myanmar Kyat', 'MMK'),

('Namibian Dollar', 'NAD'),

('Nepalese Rupee', 'NPR'),

('Netherlands Antillean Guilder', 'ANG'),

('New Zealand Dollar', 'NZD'),

('Nicaraguan Córdoba', 'NIO'),

('Nigerian Naira', 'NGN'),

('North Korean Won', 'KPW'),

('Norwegian Krone', 'NOK'),

('Omani Rial', 'OMR'),

('Pakistani Rupee', 'PKR'),

('Panamanian Balboa', 'PAB'),

('Papua New Guinean Kina', 'PGK'),

('Paraguayan Guaraní', 'PYG'),

('Peruvian Sol', 'PEN'),

('Philippine Peso', 'PHP'),

('Polish Złoty', 'PLN'),

('Qatari Riyal', 'QAR'),

('Romanian Leu', 'RON'),

('Russian Ruble', 'RUB'),

('Rwandan Franc', 'RWF'),

('Saint Helena Pound', 'SHP'),

('Samoan Tala', 'WST'),

('São Tomé and Príncipe Dobra', 'STN'),

('Saudi Riyal', 'SAR'),

('Serbian Dinar', 'RSD'),

('Seychellois Rupee', 'SCR'),

('Sierra Leonean Leone', 'SLL'),

('Singapore Dollar', 'SGD'),

('Solomon Islands Dollar', 'SBD'),

('Somali Shilling', 'SOS'),

('South African Rand', 'ZAR'),

('South Korean Won', 'KRW'),

('South Sudanese Pound', 'SSP'),

('Sri Lankan Rupee', 'LKR'),

('Sudanese Pound', 'SDG'),

('Surinamese Dollar', 'SRD'),

('Swazi Lilangeni', 'SZL'),

('Swedish Krona', 'SEK'),

('Swiss Franc', 'CHF'),

('Syrian Pound', 'SYP'),

('Taiwan Dollar', 'TWD'),

('Tajikistani Somoni', 'TJS'),

('Tanzanian Shilling', 'TZS'),

('Thai Baht', 'THB'),

('Tongan Paʻanga', 'TOP'),

('Trinidad and Tobago Dollar', 'TTD'),

('Tunisian Dinar', 'TND'),

('Turkish Lira', 'TRY'),

('Turkmenistani Manat', 'TMT'),

('Ugandan Shilling', 'UGX'),

('Ukrainian Hryvnia', 'UAH'),

('United Arab Emirates Dirham', 'AED'),

('United States Dollar', 'USD'),

('Uruguayan Peso', 'UYU'),

('Uzbekistani Som', 'UZS'),

('Vanuatu Vatu', 'VUV'),

('Venezuelan Bolívar', 'VES'),

('Vietnamese Đồng', 'VND'),

('Yemeni Rial', 'YER'),

('Zambian Kwacha', 'ZMW'),

('Zimbabwean Dollar', 'ZWL');

Python Dash App: Calculate Difference

2024-07-02T10:46:00.000-07:00

# Import necessary libraries

import dash

from dash import dcc, html

from dash.dependencies import Input, Output, State

# Initialize the Dash app

app = dash.Dash(__name__)

# Global default values

DEFAULT_VALUE1 = 1000

DEFAULT_VALUE2 = 400

# Function to calculate the difference between two values

def calculate_difference(value1, value2):

return value1 - value2

# Define the layout of the app

app.layout = html.Div([

html.Div([

html.Label('Value1'),

dcc.Input(

id='input-box-1',

type='number',

value=DEFAULT_VALUE1,

placeholder='Enter first value'

]),

html.Div([

html.Label('Value2'),

dcc.Input(

id='input-box-2',

type='number',

value=DEFAULT_VALUE2,

placeholder='Enter second value'

]),

html.Button('Update', id='update-button'),

html.Div([

html.Label('Difference:'),

html.Div(id='difference-label', style={'display': 'inline-block'})

])

# Define the callback to update the output

@app.callback(

Output('difference-label', 'children'),

Input('update-button', 'n_clicks'),

State('input-box-1', 'value'),

State('input-box-2', 'value')

)

def update_output(n_clicks, value1, value2):

if n_clicks is None:

return f'{calculate_difference(DEFAULT_VALUE1, DEFAULT_VALUE2)}'

difference = calculate_difference(float(value1), float(value2))

return f'{difference}'

# Run the app

if __name__ == '__main__':

app.run_server(debug=False)

PowerShell Function to get the last business day from today's date

2024-07-01T08:21:00.000-07:00

# Function to get the last business day from today's date

function Get-LastBusinessDay {

# Get today's date

$today = Get-Date

# Determine the last business day based on today's day of the week

switch ($today.DayOfWeek) {

'Monday' { $lastBusinessDay = $today.AddDays(-3) } # Last Friday

'Sunday' { $lastBusinessDay = $today.AddDays(-2) } # Last Friday

'Saturday' { $lastBusinessDay = $today.AddDays(-1) } # Last Friday

default { $lastBusinessDay = $today.AddDays(-1) } # Previous day

}

# Return the last business day

return $lastBusinessDay

}

# Get and print the last business day

$lastBusinessDay = Get-LastBusinessDay

Write-Output "The last business day from today's date is: $($lastBusinessDay.ToString('yyyy-MM-dd'))"

Grovey

2024-06-19T07:59:00.000-07:00

@echo off

REM Set environment variable to suppress the welcome screen

set STREAMLIT_SERVER_HEADLESS=true

set STREAMLIT_BROWSER_GATHERUSAGESTATS=false

REM Change to the directory containing your Streamlit app

cd /d "C:\path\to\your\streamlit\app"

REM Run the Streamlit app using the specified Python executable

"C:\Python39\python.exe" -m streamlit run app.py

REM Close the command prompt window after execution

exit

REM Change to the directory containing your Streamlit app

cd /d "C:\path\to\your\streamlit\app"

REM Run the Streamlit app using the specified Python executable

"C:\Python39\python.exe" -m streamlit run app.py

REM Keep the command prompt open after execution

pause

path - C:\Windows\System32\cmd.exe

C:\path\to\your\batchfile

Step 4: Configure the Service Using NSSM GUI

Path:
- In the Application tab, for the Path field, browse to your cmd.exe executable, typically located at C:\Windows\System32\cmd.exe.
Startup Directory:
- Set the Startup directory to the directory where your batch file is located, for example, C:\path\to\your\batchfile.

Arguments:

In the Arguments field, enter:

shell
/c "C:\path\to\your\batchfile\run_streamlit.bat"

I/O Redirection (Optional):
- If you want to capture the output of the batch file, go to the I/O tab and specify files for Output (stdout) and Error (stderr).
Click Install Service:

Click the Install service button.

#,##0.00;(-#,##0.00)

Step-by-Step Guide for Offline Installation of python packages

2024-06-13T02:16:00.000-07:00

Step-by-Step Guide for Offline Installation of Dash

Step 1: Download All Required Packages and Dependencies

Download Packages on an Online Machine:
```
sh
mkdir dash_packages
pip download -d dash_packages dash
```
This command downloads the dash package and all its dependencies into the dash_packages directory.

Step 2: Transfer Packages to the Offline Environment

Transfer the `dash_packages` Directory: Use a USB drive, external hard drive, or any other method to copy the `dash_packages` directory to the offline environment.

Step 3: Install Packages in the Offline Environment

Install Packages Using pip:
```
sh
pip install --no-index --find-links=dash_packages dash
```
This command tells pip to install the dash package and its dependencies from the local dash_packages directory instead of looking for them online.

Handling Complex Dependencies

In some cases, a package may have very complex dependencies. Here’s how you can ensure all dependencies are captured:

Use pip with requirements.txt:
- Create a requirements.txt file on the online machine with the package and its dependencies.
```
sh
pip freeze > requirements.txt
```
- Download all dependencies listed in requirements.txt.
```
sh
pip download -d dash_packages -r requirements.txt
```

Install from requirements.txt in the Offline Environment:

sh
pip install --no-index --find-links=dash_packages -r requirements.txt

Alternative: Using `pip wheel`

pip wheel is another tool that can be used to precompile the packages and their dependencies:

Create a Wheelhouse Directory on an Online Machine:

sh
mkdir dash_wheelhouse
pip wheel --wheel-dir=dash_wheelhouse dash

Transfer the Wheelhouse Directory: Copy the dash_wheelhouse directory to the offline environment.

Install Using Wheels:

sh
pip install --no-index --find-links=dash_wheelhouse dash

Example: Downloading and Installing Dash with Dependencies

Here’s a more detailed example assuming you want to install dash:

Download Dash and Dependencies:

sh
mkdir dash_packages
pip download -d dash_packages dash

Transfer Packages to Offline Machine: Copy the dash_packages directory to the offline machine.

Install Dash and Dependencies:

sh
pip install --no-index --find-links=dash_packages dash

Verification and Troubleshooting

Verify Installation: After installation, you can verify if the package is correctly installed by importing it in Python.
```
python
import dash
print(dash.__version__)
```
Handling Missing Dependencies: If you encounter missing dependencies, ensure all required packages are downloaded. You might need to manually add them to the dash_packages directory and rerun the installation command.

Conclusion

By carefully downloading all required packages and their dependencies, transferring them to the offline environment, and installing them locally, you can manage the installation of complex packages like `dash` without direct internet access. This method ensures you have all necessary components to run your applications smoothly.

Power BI Interview Questions and Answers

2024-05-13T05:07:00.000-07:00

Power BI Interview Questions and Answers

What is Power BI, and how does it differ from other BI tools? Answer: Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities. It allows users to connect to a wide variety of data sources, create reports and dashboards, and share insights across their organization. Unlike traditional BI tools, Power BI is cloud-based, making it easy to access and collaborate on data from anywhere.
Explain the components of Power BI. Answer: The key components of Power BI include Power Query for data preparation, Power Pivot for data modeling, Power View for data visualization, and Power Map for geospatial analysis. These components work together seamlessly to help users transform raw data into meaningful insights.
What are the different types of connections available in Power BI? Answer: Power BI supports various types of connections, including importing data from local files (such as Excel spreadsheets), connecting to on-premises data sources using the Power BI Gateway, and accessing cloud-based data sources like Azure SQL Database or Salesforce.
How do you import data into Power BI? Answer: To import data into Power BI, you can use the Get Data feature to connect to a data source, select the desired data tables or files, and then load the data into Power BI's data model. Alternatively, you can use Power Query to transform and clean the data before importing it into Power BI.
What is DAX, and why is it important in Power BI? Answer: DAX (Data Analysis Expressions) is a formula language used in Power BI for creating calculated columns, measures, and calculated tables. It allows users to perform complex calculations and analysis on their data, such as aggregations, filtering, and data manipulation.
What are calculated columns and measures in Power BI? Answer: Calculated columns are columns that are created in Power BI's data model based on formulas or expressions applied to existing columns in the data tables. Measures, on the other hand, are calculations that are dynamically evaluated based on user interactions, such as sum, average, or count.
How do you create a calculated column in Power BI? Answer: To create a calculated column in Power BI, you can use the "New Column" option in the Power Query Editor or the "New Column" feature in the Data View. You then write a DAX expression that defines the calculation for the new column based on the values in other columns.
What is the difference between calculated columns and measures? Answer: Calculated columns are static and computed during data refresh, while measures are dynamic and recalculated on the fly based on user interactions and filtering. Calculated columns are useful for adding new data elements to the data model, while measures are used for performing aggregations and calculations.
Explain the concept of relationships in Power BI. Answer: Relationships in Power BI define how data tables are connected to each other based on common fields or keys. These relationships enable users to combine data from multiple tables in a single report or visualization and perform cross-table analysis.
How do you create relationships between tables in Power BI? Answer: To create relationships between tables in Power BI, you can use the "Manage Relationships" option in the Data View or the Relationship View. You specify the fields that link the tables together, such as a primary key in one table and a foreign key in another table, and Power BI automatically creates the relationship.
What is the difference between calculated columns and measures in Power BI? Answer: Calculated columns are static and computed during data refresh, while measures are dynamic and recalculated on the fly based on user interactions and filtering. Calculated columns are useful for adding new data elements to the data model, while measures are used for performing aggregations and calculations.
Explain the concept of relationships in Power BI. Answer: Relationships in Power BI define how data tables are connected to each other based on common fields or keys. These relationships enable users to combine data from multiple tables in a single report or visualization and perform cross-table analysis.
How do you create relationships between tables in Power BI? Answer: To create relationships between tables in Power BI, you can use the "Manage Relationships" option in the Data View or the Relationship View. You specify the fields that link the tables together, such as a primary key in one table and a foreign key in another table, and Power BI automatically creates the relationship.
What are the different types of joins available in Power BI? Answer: Power BI supports various types of joins, including inner join, left outer join, right outer join, full outer join, and cross join. These join types determine how data from multiple tables is combined based on the specified join criteria.
How do you handle duplicate data in Power BI? Answer: In Power BI, you can use the Remove Duplicates feature in the Power Query Editor to eliminate duplicate rows from your dataset based on selected columns. Alternatively, you can use the DISTINCT function in DAX to remove duplicate values from a column in a table or a result set.
What is the role of the Power BI Gateway? Answer: The Power BI Gateway allows users to connect Power BI to on-premises data sources securely. It facilitates data transfer between Power BI and on-premises data sources, such as SQL Server databases or Analysis Services cubes, by establishing a connection through the gateway.
How do you schedule data refresh in Power BI? Answer: To schedule data refresh in Power BI, you can configure a refresh schedule for your dataset in the Power BI Service. You specify the frequency and time for the refresh to occur, and Power BI automatically retrieves the latest data from the underlying data sources according to the defined schedule.
What is the difference between Power BI Desktop and Power BI Service? Answer: Power BI Desktop is a standalone application used for building and authoring Power BI reports and dashboards on a local computer. Power BI Service, on the other hand, is a cloud-based platform where users can publish, share, and collaborate on Power BI content with others in their organization.
How do you create a calculated table in Power BI? Answer: To create a calculated table in Power BI, you can use the "New Table" option in the Data View and write a DAX expression that defines the calculation logic for the new table based on existing tables or columns in the data model.
What is the difference between calculated columns and calculated tables? Answer: Calculated columns are added to existing tables in the data model and are computed row by row during data refresh, while calculated tables are created as new tables in the data model based on DAX expressions and are computed once during data model processing.
How do you handle errors in Power BI data transformations? Answer: In Power BI, you can use error handling functions like IFERROR, ISERROR, and TRY...CATCH to manage errors during data transformations. These functions allow you to handle errors gracefully by providing fallback values or alternative processing logic.
What is the Power Query Editor, and how do you use it? Answer: The Power Query Editor is a data preparation tool in Power BI used for transforming and cleaning data before loading it into the data model. You can use the Power Query Editor to perform various data transformation tasks such as filtering, sorting, merging, and appending data from different sources.
How do you create a calculated measure in Power BI? Answer: To create a calculated measure in Power BI, you can use the "New Measure" option in the Data View or the "New Measure" button in the Modeling tab. You then write a DAX expression that defines the calculation logic for the measure based on the data fields in the data model.
What is the difference between calculated columns and calculated measures? Answer: Calculated columns are computed row by row during data refresh and are stored in the data model, while calculated measures are dynamically evaluated based on user interactions and are not stored in the data model. Calculated columns are useful for adding new data elements, while measures are used for performing aggregations and calculations.
How do you create a hierarchy in Power BI? Answer: To create a hierarchy in Power BI, you can use the "New Hierarchy" option in the Data View or the "New Hierarchy" button in the Modeling tab. You then select the fields you want to include in the hierarchy and arrange them in the desired order.
What is row-level security in Power BI, and how do you implement it? Answer: Row-level security in Power BI allows you to restrict access to data at the row level based on user roles or permissions. You can implement row-level security by defining security roles in the Power BI Service and specifying filters or rules that determine which rows of data users can access.
What are the different types of filters in Power BI, and how do you apply them? Answer: Power BI supports various types of filters, including visual filters, page filters, report filters, and slicers. You can apply filters to your report or visualization by selecting the fields you want to filter on and specifying the filter criteria or conditions.
How do you create a calculated column using an IF statement in Power BI? Answer: To create a calculated column using an IF statement in Power BI, you can use the "New Column" option in the Power Query Editor or the "New Column" feature in the Data View. You then write a DAX expression that uses the IF function to conditionally calculate values based on specified criteria.
What is the difference between Power BI Desktop and Power BI Report Server? Answer: Power BI Desktop is a standalone application used for building and authoring Power BI reports and dashboards on a local computer, while Power BI Report Server is an on-premises reporting solution that allows organizations to host and manage Power BI reports and dashboards within their own environment.
How do you create a calculated table using DAX in Power BI? Answer: To create a calculated table using DAX in Power BI, you can use the "New Table" option in the Data View and write a DAX expression that defines the calculation logic for the new table based on existing tables or columns in the data model.

Will Add more in the upcoming days.

Basic Python interview questions - Set 1 - 55 Questions and Answers

2024-05-08T23:11:00.000-07:00

What is Python?
- Python is a high-level, interpreted programming language known for its simplicity and readability. It supports multiple programming paradigms, including procedural, object-oriented, and functional programming.
What are the key features of Python?
- Some key features of Python include:
  - Easy-to-read syntax
  - Dynamic typing
  - Automatic memory management (garbage collection)
  - Extensive standard library
  - Cross-platform compatibility
What are the differences between Python 2 and Python 3?
- Python 3 is the latest version of the language and has several improvements over Python 2, including:
  - Print function: In Python 3, print is a function whereas in Python 2, it's a statement.
  - Unicode: Python 3 handles strings as Unicode by default, while Python 2 treats strings as ASCII by default.
  - Division: In Python 3, division of integers produces floating-point results by default, while in Python 2, it produces integer results (unless from __future__ import division is used).
  - Various library changes and improvements.
What are decorators in Python?
- Decorators are a powerful feature in Python that allow you to dynamically modify the behavior of functions or methods at runtime. They are implemented using the @decorator_name syntax.
Explain the difference between a list and a tuple in Python.
- Lists are mutable, meaning their elements can be changed after creation, while tuples are immutable, meaning their elements cannot be changed after creation.
- Lists are defined using square brackets [], while tuples are defined using parentheses ().
What is a generator in Python?
- A generator in Python is a special type of iterator that generates values on-the-fly using the yield keyword. Generators are memory-efficient and are commonly used for generating large sequences of values.
Explain the difference between __str__ and __repr__ in Python.
- __str__ is called by the str() function and is intended to return a string representation of the object for end-users. __repr__ is called by the repr() function and is intended to return an unambiguous string representation of the object for developers.
What is the purpose of if __name__ == "__main__": in Python scripts?
- This conditional statement checks whether the Python script is being run as the main program or if it is being imported as a module. It allows you to write code that will only be executed when the script is run directly, not when it's imported as a module.
How do you handle exceptions in Python?
- Exceptions in Python are handled using try, except, finally blocks. Code that may raise an exception is placed inside the try block, and exception handling code is placed inside the except block. The finally block is used for code that should be executed regardless of whether an exception occurred or not.
What are some common built-in data types in Python?

Some common built-in data types in Python include:

Integers (int)
Floating-point numbers (float)
Strings (str)
Lists (list)
Tuples (tuple)
Dictionaries (dict)
Sets (set)
Boolean (bool)

What is PEP 8?
- PEP 8 is the style guide for Python code, outlining coding conventions and recommendations for writing clean, readable code.
Explain the difference between == and is operators in Python.
- The == operator checks for equality of values, while the is operator checks for identity, i.e., whether two variables refer to the same object in memory.
What is a lambda function in Python?
- A lambda function, also known as an anonymous function, is a small, anonymous function defined using the lambda keyword. It can take any number of arguments, but can only have one expression.
What are the advantages of using Python for web development?
- Python's simplicity, readability, and extensive libraries make it well-suited for web development. Frameworks like Django and Flask provide tools for building scalable and maintainable web applications.
How do you handle file I/O in Python?
- File I/O in Python is handled using the open() function to open files, and methods like read(), write(), close(), etc., to read from or write to files.
What is the purpose of the __init__ method in Python classes?
- The __init__ method is a special method in Python classes used for initializing new objects. It is called when an instance of the class is created.
Explain the difference between append() and extend() methods in Python lists.
- The append() method adds a single element to the end of a list, while the extend() method adds all the elements of an iterable (such as another list) to the end of the list.
How do you iterate over a dictionary in Python?
- You can iterate over a dictionary using a for loop, accessing keys, values, or key-value pairs using methods like keys(), values(), and items().
What are list comprehensions in Python?
- List comprehensions provide a concise way to create lists in Python by applying an expression to each item in an iterable.
Explain the difference between shallow copy and deep copy in Python.
- A shallow copy creates a new object but inserts references to the original elements into it. A deep copy creates a new object and recursively inserts copies of the original elements into it.
What are decorators used for in Python?
- Decorators are used to modify the behavior of functions or methods without changing their code. They are often used for aspects such as logging, authentication, or caching.
What is the purpose of the global keyword in Python?
- The global keyword is used inside a function to declare that a variable inside the function should refer to the globally scoped variable with the same name.
How do you handle multiple exceptions in Python?
- Multiple exceptions in Python can be handled using multiple except blocks, a single except block with a tuple of exception types, or a catch-all except block.
What is the purpose of the __doc__ attribute in Python?
- The __doc__ attribute is used to access the docstring of a Python object, which provides documentation about the object's purpose and usage.
How do you create a virtual environment in Python?
- You can create a virtual environment in Python using the venv module, which is included in the Python standard library since version 3.3.
What is monkey patching in Python?
- Monkey patching is the practice of dynamically modifying or extending code at runtime. It's often used for adding or modifying behavior in third-party libraries or frameworks.
What is the purpose of the __name__ variable in Python?
- The __name__ variable is a special variable in Python that contains the name of the current module. When a Python script is run as the main program, its __name__ variable is set to '__main__'.
Explain the difference between __getitem__ and __iter__ methods in Python.
- __getitem__ is a method used to implement indexing in Python objects, allowing them to be accessed using square brackets []. __iter__ is a method used to make an object iterable, allowing it to be used in a for loop.
What is the purpose of the super() function in Python?
- The super() function is used to call methods and constructors of the parent class from a subclass. It's commonly used to initialize parent class attributes in the subclass constructor.
What is a metaclass in Python?
- A metaclass is a class whose instances are classes themselves. Metaclasses are used to define the behavior of classes, such as how they are constructed or how their methods are inherited.
What is the GIL (Global Interpreter Lock) in Python?
- The GIL is a mutex that protects access to Python objects, preventing multiple native threads from executing Python bytecode simultaneously. This can limit the parallelism of Python programs, particularly CPU-bound ones.
How do you sort a dictionary by value in Python?
- Dictionaries in Python are inherently unordered, but you can sort them by value using the sorted() function along with a custom sorting key.
Explain the difference between shallow copy and deep copy in Python.
- A shallow copy creates a new object but inserts references to the original elements into it. A deep copy creates a new object and recursively inserts copies of the original elements into it.
What is a context manager in Python?
- A context manager in Python is an object that is used to manage resources, such as files or database connections, in a way that ensures they are properly released when they are no longer needed. Context managers are implemented using the with statement.
What is the purpose of the __slots__ attribute in Python?
- The __slots__ attribute is used to explicitly declare the attributes of a class, preventing the creation of dynamic attributes. This can reduce memory usage and improve performance for classes with a fixed set of attributes.
How do you handle missing values in pandas?
- Missing values in pandas are typically represented as NaN (Not a Number) or None. You can handle missing values using methods like dropna(), fillna(), or isnull().
What is the purpose of the __call__ method in Python?
- The __call__ method allows an object to be called as if it were a function. It's commonly used to implement callable objects, such as function-like classes or decorators.
What are the differences between a list and a tuple in Python?
- Lists are mutable, meaning their elements can be changed after creation, while tuples are immutable, meaning their elements cannot be changed after creation. Lists are defined using square brackets [], while tuples are defined using parentheses ().
What are the advantages of using generators in Python?
- Generators in Python are memory-efficient and allow for lazy evaluation, meaning they generate values on-the-fly rather than storing them in memory. This can be useful for processing large datasets or infinite sequences.
How do you handle regular expressions in Python?
- Regular expressions in Python are handled using the re module, which provides functions for pattern matching, searching, and substitution.
What is the purpose of the @staticmethod decorator in Python?
- The @staticmethod decorator is used to define a static method in a class. Static methods do not have access to the instance (self) or class (cls) variables and are primarily used for utility functions that do not depend on instance state.
What is the purpose of the @property decorator in Python?
- The @property decorator is used to define a property in a class that behaves like an attribute but allows for custom getter, setter, and deleter methods.
What are docstrings in Python?
- Docstrings are strings enclosed in triple quotes (''' or """) that are used to provide documentation for functions, classes, or modules in Python. They are accessible via the __doc__ attribute.
What is the purpose of the __slots__ attribute in Python?
- The __slots__ attribute is used to explicitly declare the attributes of a class, preventing the creation of dynamic attributes. This can reduce memory usage and improve performance for classes with a fixed set of attributes.
How do you install third-party packages in Python?
- Third-party packages in Python can be installed using package managers like pip, which is the standard package manager for Python. You can install packages by running pip install package_name.
What are some common design patterns used in Python?
- Some common design patterns used in Python include Singleton, Factory, Observer, Strategy, and Iterator patterns. These patterns provide solutions to common problems in software design.
How do you handle errors and exceptions in Python?
- Errors and exceptions in Python are handled using try, except, finally, and optionally else blocks. Code that may raise an exception is placed inside the try block, and exception handling code is placed inside the except block.
What is monkey patching in Python?
- Monkey patching is the practice of dynamically modifying or extending code at runtime. It's often used for adding or modifying behavior in third-party libraries or frameworks.
What is the purpose of the __getattr__ and __setattr__ methods in Python?
- The __getattr__ method is called when an attribute is accessed that does not exist, allowing you to define custom behavior for attribute access. The __setattr__ method is called when an attribute is assigned a value, allowing you to intercept and modify attribute assignments.
What is the purpose of the enumerate() function in Python?
- The enumerate() function is used to iterate over a sequence (such as a list or tuple) while keeping track of the index of each item. It returns an iterator of tuples containing the index and the corresponding item.
What is the purpose of the async and await keywords in Python?
- The async and await keywords are used for asynchronous programming in Python. They allow you to define asynchronous functions and await the results of asynchronous operations without blocking the event loop.
What is the purpose of the functools module in Python?
- The functools module in Python provides higher-order functions and operations on callable objects. It includes utilities for function composition, memoization, and partial function application.
How do you handle timezones in Python?
- Timezones in Python are handled using the datetime module, which provides classes like datetime and timezone for working with dates, times, and timezones. The pytz library can also be used for more comprehensive timezone support.
What is the purpose of the collections module in Python?
- The collections module in Python provides specialized container datatypes, such as Counter, deque, namedtuple, and defaultdict, which are useful for various tasks such as counting elements, creating ordered collections, and handling dictionaries with default values.
What is the purpose of the argparse module in Python?
- The argparse module in Python is used for parsing command-line arguments. It provides a convenient way to define and parse command-line options and arguments, automatically generating help messages and usage documentation.

R - For Loop API upload - Key

2023-11-23T23:17:00.000-08:00

# Load the necessary packages: httr and jsonlite

install.packages("httr") install.packages("jsonlite") library(httr) library(jsonlite) library(dplyr) # Set up the API endpoint URL and the API key as variables. url <- "https://www.reed.co.uk/api/1.0/search?keywords=analyst&locationName=London&distancefromlocation=55&employerId=575264&resultsToTake=100" api_key <- authenticate( user = "ADD API KEY HERE", password = "", type = "basic") # Create an empty list to store the API responses. responses <- list() # Set up a for loop that iterates over a sequence of values that you want to query the API for. seq(from = 1, to = 1001, by = 100) # Create a sequence going up by 100 for (i in seq(from = 1, to = 1001, by = 100)) { # Construct the API request URL with the i value and the API key req_url <- paste0(url, "&resultsToSkip=", i) # Send the API request using the GET() function from the httr package response <- GET(req_url, api_key) # Parse the JSON response using the fromJSON() function from the jsonlite package response_json <- fromJSON(content(response, "text")) # Store the response in the list responses[[i]] <- response_json$results } # View first table in the list View(responses[[1]]) # View second table in the list - no data as we skipped to 101 View(responses[[2]]) # View table 101 in the list - second table with data View(responses[[101]]) # Combine all tables in the list that have data using bind_rows in dplyr jobs <- bind_rows(responses)

API - Reed.co.uk - R Code - Key Req

2023-11-23T23:16:00.000-08:00

# Install packages httr and jsonlite

install.packages("httr") install.packages("jsonlite") # Create a GET response to call the API ```{r} jobsearch <- httr::GET("https://www.reed.co.uk/api/1.0/search?keywords=analyst&location=london&distancefromlocation=15?details=true", httr::authenticate( user = "686dc8ba-b318-4d65-ae64-7387613517230000000000", password = "", type = "basic")) # View API GET response result str(jobsearch) # View the main source of the data needed from the API - Normally content str(jobsearch$content) ``` # Convert the raw content to text (in this case, a JSON string) ```{r} jobsearchcontent <- httr::content(jobsearch, as = "text") # View JSON String result str(jobsearchcontent) ``` # Convert the JSON string to a dataframe and view data in a table ```{r} jobsearchJSON <- jsonlite::fromJSON(jobsearchcontent) View(jobsearchJSON$results) ```

API - Give Food - No key - R code

2023-11-23T23:15:00.000-08:00

# Install packages httr and jsonlite

```{r} install.packages("httr") install.packages("jsonlite") ``` # Create a GET response to call the API ```{r} foodbank <- httr::GET("https://www.givefood.org.uk/api/2/foodbanks/") # View API GET response result str(foodbank) # View the main source of the data needed from the API - Normally content str(foodbank$content) ``` # Convert the raw content to text (in this case, a JSON string) ```{r} foodbankcontent <- httr::content(foodbank, as = "text") # View JSON String result str(foodbankcontent) ``` # Convert the JSON string to a dataframe and view data in a table ```{r} foodbankJSON <- jsonlite::fromJSON(foodbankcontent) View(foodbankJSON) ```

test

2023-11-23T08:57:00.000-08:00

..

https://ubiquis.co.uk/loading-csv-files-with-pdi-metadata-injection-2/
-- Create a temporary table to demonstrate the solution
CREATE TABLE YourTable (
    ID INT,
    YourColumn VARCHAR(100)
);

-- Insert sample data
INSERT INTO YourTable (ID, YourColumn) VALUES
(1, '1234_4567_abc'),
(2, '789_012_def'),
(3, 'xyz');

-- Declare variables for storing results
DECLARE @firstPart VARCHAR(100), @secondPart VARCHAR(100);

-- Iterate over rows in the table
DECLARE @currentID INT = 1;
DECLARE @rowCount INT = (SELECT COUNT(*) FROM YourTable);

WHILE @currentID <= @rowCount
BEGIN
    -- Retrieve input string from the table
    DECLARE @inputString VARCHAR(100);
    SELECT @inputString = YourColumn FROM YourTable WHERE ID = @currentID;

    -- Check if the delimiter exists in the input string
    IF CHARINDEX('_', @inputString) > 0
    BEGIN
        -- Find the position of the first occurrence of the delimiter in the input string
        DECLARE @delimiterIndex INT = CHARINDEX('_', @inputString);

        -- Extract the first part before the delimiter
        SET @firstPart = LEFT(@inputString, @delimiterIndex - 1);

        -- Find the position of the second occurrence of the delimiter starting from the position after the first occurrence
        DECLARE @secondDelimiterIndex INT = CHARINDEX('_', @inputString, @delimiterIndex + 1);

        -- Extract the second part between the first and second delimiters
        IF @secondDelimiterIndex > 0
        BEGIN
            SET @secondPart = SUBSTRING(@inputString, @delimiterIndex + 1, @secondDelimiterIndex - @delimiterIndex - 1);
        END
        ELSE
        BEGIN
            -- If second delimiter not found, take the substring from the position after the first delimiter to the end of the string
            SET @secondPart = SUBSTRING(@inputString, @delimiterIndex + 1, LEN(@inputString) - @delimiterIndex);
        END

        -- Output the results
        PRINT 'ID: ' + CAST(@currentID AS VARCHAR(10)) + ', FirstPart: ' + @firstPart + ', SecondPart: ' + @secondPart;
    END
    ELSE
    BEGIN
        -- Handle case when delimiter is not found
        PRINT 'ID: ' + CAST(@currentID AS VARCHAR(10)) + ', Delimiter not found in the input string.';
    END

    -- Move to the next row
    SET @currentID = @currentID + 1;
END

-- Drop the temporary table
DROP TABLE YourTable;

------------------------------------

-- Create a temporary table to demonstrate the solution
CREATE TABLE YourTable (
    ID INT,
    YourColumn VARCHAR(100)
);

-- Insert sample data
INSERT INTO YourTable (ID, YourColumn) VALUES
(1, '1234_4567_abc'),
(2, '789_012_def'),
(3, 'xyz');

-- Query to split the input string into two parts
SELECT
    ID,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 THEN LEFT(YourColumn, CHARINDEX('_', YourColumn) - 1) 
        ELSE YourColumn 
    END AS FirstPart,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 THEN SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, LEN(YourColumn) - CHARINDEX('_', YourColumn)) 
        ELSE NULL 
    END AS SecondPart
FROM
    YourTable;

========================

-- Create a temporary table to demonstrate the solution
CREATE TABLE YourTable (
    ID INT,
    YourColumn VARCHAR(100)
);

-- Insert sample data
INSERT INTO YourTable (ID, YourColumn) VALUES
(1, '1234_4567_abc'),
(2, '789_012_def'),
(3, 'xyz'),
(4, '123_456');

-- Query to split the input string into two parts
-- Query to split the input string into two parts
SELECT
    ID,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 THEN LEFT(YourColumn, CHARINDEX('_', YourColumn) - 1) 
        ELSE YourColumn 
    END AS FirstPart,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 
            THEN 
                CASE 
                    WHEN CHARINDEX('_', YourColumn, CHARINDEX('_', YourColumn) + 1) > 0
                        THEN
                            CASE 
                                WHEN TRY_CAST(SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, CHARINDEX('_', YourColumn, CHARINDEX('_', YourColumn) + 1) - CHARINDEX('_', YourColumn) - 1) AS INT) IS NOT NULL 
                                    THEN SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, CHARINDEX('_', YourColumn, CHARINDEX('_', YourColumn) + 1) - CHARINDEX('_', YourColumn) - 1) 
                                ELSE NULL 
                            END
                    ELSE 
                        CASE 
                            WHEN TRY_CAST(SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, LEN(YourColumn) - CHARINDEX('_', YourColumn)) AS INT) IS NOT NULL 
                                THEN SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, LEN(YourColumn) - CHARINDEX('_', YourColumn)) 
                            ELSE NULL 
                        END
                END
        ELSE NULL 
    END AS SecondPart
FROM
    YourTable;

// Input field containing the full file path
var fullpathlocation = 'C:\\Folder1\\folder2\\abc.csv'; // Example input

// Extract directory path from full file path
var directoryPath = fullpathlocation.substring(0, fullpathlocation.lastIndexOf('\\') + 1);

// Set the result to the output field
output_field = directoryPath;



====================================================


DECLARE @RowCount INT = 1;
DECLARE @TotalRows INT;

--CREATE TABLE FundConfig (FundName VARCHAR(255), EMAIL VARCHAR(200))

--INSERT INTO FundConfig VALUES
--('A', 'a@gmail.com')
--,('B', 'b@gmail.com')
--,('C', 'c@gmail.com')


-- Create a temporary table to hold FundName and Email along with row numbers
SELECT FundName, Email, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
INTO  #TempTable
FROM FundConfig;

-- Get the total number of rows
SELECT @TotalRows = COUNT(*) FROM #TempTable;

-- Loop through each row using a WHILE loop
WHILE @RowCount <= @TotalRows
BEGIN
    -- Retrieve FundName and Email for the current row
    SELECT FundName, Email
    FROM #TempTable
    WHERE RowNum = @RowCount;

    -- Increment row counter
    SET @RowCount = @RowCount + 1;
END

-- Drop the temporary table
DROP TABLE #TempTable;


===============

SELECT 
    ID,
    STRING_AGG(CAST(Value AS VARCHAR), ';') WITHIN GROUP (ORDER BY Value) AS Value
FROM 
    YourTableName
GROUP BY 
    ID;


=======================

WITH Dates AS (
    SELECT DISTINCT Date FROM Logtable
),
Funds AS (
    SELECT DISTINCT Fundname FROM Logtable
)
SELECT d.Date, f.Fundname
FROM Dates d
CROSS JOIN Funds f
LEFT JOIN Logtable lt ON d.Date = lt.Date AND f.Fundname = lt.Fundname
WHERE lt.Fundname IS NULL
ORDER BY d.Date, f.Fundname;


DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-31';

WITH DateRange AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date)
    FROM DateRange
    WHERE DATEADD(day, 1, Date) <= @EndDate
)
SELECT Date
FROM DateRange
WHERE DATEPART(dw, Date) NOT IN (1, 7); -- Filter out Sundays (1) and Saturdays (7)




==========================


import os
import shutil
import pandas as pd
from openpyxl import load_workbook

def remove_password_protection(file_path):
    try:
        # Load the Excel workbook
        wb = load_workbook(filename=file_path)

        # Remove password protection
        for sheet in wb.sheetnames:
            ws = wb[sheet]
            ws.protection.password = None

        # Save the workbook without password protection
        wb.save(file_path)
        print(f"Password protection removed from {file_path}")
    except Exception as e:
        print(f"Error occurred while removing password protection from {file_path}: {e}")

def convert_and_move_files(source_folder, destination_folder):
    # Create destination folder if it doesn't exist
    if not os.path.exists(destination_folder):
        os.makedirs(destination_folder)

    # List files in the source folder
    files = os.listdir(source_folder)

    for file in files:
        if file.endswith('.xls'):
            # Remove password protection if any
            input_file_path = os.path.join(source_folder, file)
            remove_password_protection(input_file_path)

            # Convert xls to xlsx
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file.replace('.xls', '.xlsx'))
            try:
                df = pd.read_excel(input_file)
                df.to_excel(output_file, index=False)
                print(f"Converted {file} to {output_file}")

                # Remove the original xls file
                os.remove(input_file)
            except Exception as e:
                print(f"Error occurred during conversion of {file}: {e}")
        
        elif file.endswith('.xlsx'):
            # Directly move the xlsx file to destination folder
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file)
            try:
                shutil.move(input_file, output_file)
                print(f"Moved {file} to {destination_folder}")
            except Exception as e:
                print(f"Error occurred during moving of {file}: {e}")

# Replace 'source_folder' and 'destination_folder' with your folder paths
source_folder = 'source_folder'
destination_folder = 'destination_folder'

convert_and_move_files(source_folder, destination_folder)


====================

import os
import shutil
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

import zipfile
from openpyxl import load_workbook

def remove_password_protection(file_path, password='1234'):
    try:
        with open(file_path, 'rb') as f:
            data = f.read()
        # Locate the password hash in the encrypted file
        if data[0x20:0x24] == b'\x2F\xE5\x4F\x3C':
            data = data[0x20:]
        # Replace the hash with null bytes
        data = data.replace(data[0x12:0x2E], b'\x00' * 14)
        # Write the modified data back to the file
        with open(file_path, 'wb') as f:
            f.write(data)
        print(f"Password protection removed from {file_path}")
    except Exception as e:
        print(f"Error occurred while removing password protection from {file_path}: {e}")


def convert_and_move_files(source_folder, destination_folder):
    # Create destination folder if it doesn't exist
    if not os.path.exists(destination_folder):
        os.makedirs(destination_folder)

    # List files in the source folder
    files = os.listdir(source_folder)

    for file in files:
        if file.endswith('.xls'):
            # Remove password protection if any
            input_file_path = os.path.join(source_folder, file)
            remove_password_protection(input_file_path)

            # Convert xls to xlsx
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file.replace('.xls', '.xlsx'))
            try:
                # Read data from xls using pandas and save to xlsx using openpyxl
                df = pd.read_excel(input_file)
                with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
                    df.to_excel(writer, index=False)
                    writer.save()
                print(f"Converted {file} to {output_file}")

                # Remove the original xls file
                os.remove(input_file)
            except Exception as e:
                print(f"Error occurred during conversion of {file}: {e}")
        
        elif file.endswith('.xlsx'):
            # Directly move the xlsx file to destination folder
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file)
            try:
                shutil.move(input_file, output_file)
                print(f"Moved {file} to {destination_folder}")
            except Exception as e:
                print(f"Error occurred during moving of {file}: {e}")

# Replace 'source_folder' and 'destination_folder' with your folder paths
source_folder = 'source_folder'
destination_folder = 'destination_folder'

convert_and_move_files(source_folder, destination_folder)

====


import os
import shutil
from pyexcelerate import Workbook
from xlrd import open_workbook
from xlutils.copy import copy

def remove_password_protection(file_path, password):
    try:
        # Create a new workbook using pyexcelerate
        new_wb = Workbook()
        # Load the original workbook using xlrd
        rb = open_workbook(file_path, formatting_info=True, on_demand=True, password=password)
        # Create a copy of the workbook to write changes
        wb = copy(rb)
        # Iterate through each sheet and add to the new workbook
        for sheet_index in range(wb.nsheets):
            sheet = wb.get_sheet(sheet_index)
            new_wb.new_sheet(sheet.name, data=sheet._cell_values)
        # Save the new workbook to a temporary file
        temp_file_path = file_path + '.tmp.xlsx'
        new_wb.save(temp_file_path)

        # Replace the original file with the modified one
        shutil.move(temp_file_path, file_path)
        print(f"Password protection removed from {file_path}")
    except Exception as e:
        print(f"Error occurred while removing password protection from {file_path}: {e}")

# Replace 'source_folder' with your folder path containing the password-protected .xls files
source_folder = 'source_folder'
password = 'your_password'

# List files in the source folder
files = os.listdir(source_folder)

for file in files:
    if file.endswith('.xls'):
        # Remove password protection if any
        input_file_path = os.path.join(source_folder, file)
        remove_password_protection(input_file_path, password)





======================================


# Define variables
$folderPath = "C:\Path\To\Your\Folder"
$timeLimit = (Get-Date).AddHours(-1)
$emailTo = "recipient@example.com"
$emailFrom = "sender@example.com"
$smtpServer = "smtp.example.com"
$smtpPort = 587
$smtpUsername = "your_smtp_username"
$smtpPassword = "your_smtp_password"
$emailSubject = "No New Files Detected"
$emailBody = "No new files have been detected in the folder '$folderPath' within the last hour."

# Check for new files in the folder
$newFiles = Get-ChildItem -Path $folderPath | Where-Object { $_.LastWriteTime -gt $timeLimit }

# If no new files are found, send an email notification
if ($newFiles.Count -eq 0) {
    $smtpSecurePassword = ConvertTo-SecureString $smtpPassword -AsPlainText -Force
    $credentials = New-Object System.Management.Automation.PSCredential ($smtpUsername, $smtpSecurePassword)
    
    Send-MailMessage -To $emailTo `
                     -From $emailFrom `
                     -Subject $emailSubject `
                     -Body $emailBody `
                     -SmtpServer $smtpServer `
                     -Port $smtpPort `
                     -UseSsl `
                     -Credential $credentials
}

Write-Output "Script executed at: $(Get-Date)"





=========================


DECLARE @InputString NVARCHAR(MAX) = 'A,B,C,D'
DECLARE @Delimiter CHAR(1) = ','

;WITH SplitString AS
(
    SELECT 
        LEFT(@InputString, CHARINDEX(@Delimiter, @InputString + @Delimiter) - 1) AS Part,
        SUBSTRING(@InputString, CHARINDEX(@Delimiter, @InputString + @Delimiter) + 1, LEN(@InputString)) AS Remainder
    UNION ALL
    SELECT 
        LEFT(Remainder, CHARINDEX(@Delimiter, Remainder + @Delimiter) - 1),
        SUBSTRING(Remainder, CHARINDEX(@Delimiter, Remainder + @Delimiter) + 1, LEN(Remainder))
    FROM 
        SplitString
    WHERE 
        Remainder <> ''
)
SELECT 
    Part
FROM 
    SplitString
OPTION (MAXRECURSION 0);

-====================


import pyodbc

# Define your connection parameters
server = 'your_server_name'  # e.g., 'localhost\\SQLEXPRESS' or 'your_server_ip'
database = 'your_database_name'  # e.g., 'master'
username = 'your_username'  # e.g., 'sa'
password = 'your_password'  # e.g., 'your_password'

# Define the connection string
connection_string = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Establish the connection
try:
    connection = pyodbc.connect(connection_string)
    print("Connection successful!")

    # Create a cursor object
    cursor = connection.cursor()

    # Execute a simple query
    cursor.execute("SELECT @@version;")
    row = cursor.fetchone()
    while row:
        print(row[0])
        row = cursor.fetchone()

except pyodbc.Error as e:
    print("Error in connection:", e)
finally:
    # Close the connection
    if 'connection' in locals() and connection:
        connection.close()

ISIN Validation SQL Function

2023-09-10T21:57:00.001-07:00

CREATE FUNCTION dbo.IsValidISIN(@ISIN NVARCHAR(12))

RETURNS INT AS BEGIN

DECLARE @Check INT

--Check digit not available

IF RIGHT(@ISIN,1) NOT BETWEEN '0' AND '9' BEGIN

SET @Check=-1

RETURN @Check

END

IF PATINDEX('%[^0-Z]%',@ISIN)>0 BEGIN

SET @Check=-3

RETURN @Check

END

DECLARE @Data NVARCHAR(30)=LEFT(@ISIN,11)

DECLARE @MultOdd INT= 1, @MultEve INT= 1

DECLARE @Sum INT=0,@StrOdd NVARCHAR(60)='',@StrEve NVARCHAR(60)=''

DECLARE @Letter INT,@Text VARCHAR(1)

SET @Letter =PATINDEX('%[A-Z]%',@Data)

SET @Text=SUBSTRING(@Data,@Letter,1) BEGIN

WHILE @Letter>0 BEGIN

SET @Data=STUFF(@Data,@Letter,1,ASCII(UPPER(@Text))-55)

SET @Letter =PATINDEX('%[A-Z]%',@Data)

SET @Text=SUBSTRING(@Data,@Letter,1)

END

--Get Length Multiplier

IF LEN(@Data)%2=0 BEGIN

SET @MultEve = 2;

END

ELSE BEGIN

SET @MultOdd = 2;

SET @Letter = 1;

END

WHILE @Letter<=LEN(@Data) BEGIN

IF @Letter%2=1 BEGIN

SET @StrOdd=@StrOdd+CONVERT(VARCHAR(60),CONVERT(INT,SUBSTRING(@Data,@Letter,1))*@MultOdd);

END

ELSE BEGIN

SET @StrEve=@StrEve+CONVERT(VARCHAR(60),CONVERT(INT,SUBSTRING(@Data,@Letter,1))*@MultEve);

END

SET @Letter=@Letter+1

END

SET @Data=@StrOdd+@StrEve

SET @Letter = 1

WHILE @Letter<=LEN(@Data) BEGIN

SET @Sum=@Sum+CONVERT(INT,SUBSTRING(@Data,@Letter,1))

SET @Letter=@Letter+1

END

IF LEN(@ISIN)<>12 BEGIN

SET @Check=-4

RETURN @Check

END

IF ISNULL(@Check,0)=0 BEGIN

SET @Check =(SELECT (CASE WHEN RIGHT(@ISIN,1)=CONVERT(VARCHAR(1),(10-(@Sum%10))%10) THEN 1 ELSE 0 END));

END

RETURN @Check

END

xml example

2023-05-09T07:50:00.007-07:00

library(httr) # Define the URL of the API endpoint url <- "https://your.api.endpoint" # Define the data to be sent in the POST request (replace with your actual data) data <- list( key1 = "value1", key2 = "value2" ) # Define the token (replace with your actual token) token <- "your_access_token" # Create the POST request with headers response <- POST( url, body = data, add_headers("Authorization" = paste("Bearer", token)), encode = "json" # Specify the encoding, e.g., "json" ) # Check the response status_code <- status_code(response) content <- content(response) cat("Status Code:", status_code, "\n") cat("Response Content:", content, "\n")

XML row count using python

2023-05-09T07:46:00.002-07:00

# import required module

import os

import xml.etree.ElementTree as ET

# assign directory

directory = 'D:\\XML\\Data'

# iterate over files in

# that directory

for filename in os.listdir(directory):

f = os.path.join(directory, filename)

# checking if it is a file

if os.path.isfile(f):

tree = ET.parse(f)

root = tree.getroot()

num_rows = len(root.findall('.//Product'))

print(f, num_rows)

Migrating SQL Server Reporting Services (SSRS) from a busy server to a dedicated server can provide several benefits

2023-05-01T06:51:00.006-07:00

Migrating SQL Server Reporting Services (SSRS) from a busy server to a dedicated server can provide several benefits, including:

Improved performance: When SSRS is running on a busy server that is also hosting other applications, it can cause performance issues for SSRS. Moving SSRS to a dedicated server can improve the performance of reports and reduce the time it takes to generate and deliver them.

Scalability: A dedicated SSRS server can be more easily scaled than a busy server that is hosting multiple applications. As your reporting needs grow, you can add more resources to the dedicated server to ensure that it can handle the increased load.

Better resource management: By moving SSRS to a dedicated server, you can better manage the resources allocated to reporting. You can configure the server to optimize its performance for SSRS, which can result in more efficient use of resources and faster report generation times.

Increased security: By separating SSRS from other applications on the server, you can improve the security of your reporting environment. This can help to prevent unauthorized access to your reports and data.

Simplified management: Having SSRS running on a dedicated server can simplify the management of your reporting environment. You can focus on optimizing the server for SSRS and not have to worry about how other applications may be affecting its performance.

Overall, migrating SSRS from a busy server to a dedicated server can provide several benefits, including improved performance, scalability, better resource management, increased security, and simplified management.

These are some of the key prerequisites for migrating SSRS from one server to another. By completing these steps carefully, you can ensure a successful SSRS migration with minimal downtime and data loss.

Python code to unprotect the excel sheet with a valid password

2023-04-13T02:56:00.009-07:00

import msoffcrypto

import pathlib

# pip install msoffcrypto-tool'

url = pathlib.Path(r'D:\Source')

excel_files = list(url.glob('*.xlsx'))

def unlock(filename, passwd, output_folder):

temp = open(filename, 'rb')

excel = msoffcrypto.OfficeFile(temp)

excel.load_key(passwd)

out_path = pathlib.Path(output_folder)

if not out_path.exists():

out_path.mkdir()

with open(str(out_path/filename.name), 'wb') as f:

excel.decrypt(f)

temp.close()

for i in excel_files:

unlock(i, 'Password', r'D:\Destination')

------------------------------

# Replace "C:\Your\Directory\Path" with the actual path to the directory you want to search in.

$directoryPath = "D:\Testing"

# Replace "2023-06-30" with the date you want to check in "YYYY-MM-DD" format.

$targetDate = "2023-06-30"

# Get all the *.txt files in the specified directory that match the target date.

$matchingFiles = Get-ChildItem -Path $directoryPath -Filter "*.txt" | Where-Object { $_.LastWriteTime.Date -eq (Get-Date $targetDate).Date }

# Display the count of matching files.

Write-Host "Number of *.txt files for $targetDate $($matchingFiles.Count)"

MSBI - Business Intelligence

host

requirement.txt

Optimizing Tableau Dashboard Performance in Web Applications: Strategies for Caching and Faster Load Times"

T

Steps to create SSH key from git bash

Generating an SSH key in OpenSSH involves a few simple steps. Here’s how you can do it:

1. Open a Terminal

If you're on Linux or macOS, you can use the built-in terminal.On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed.

2. Generate the SSH Key Pair

3. Choose a File Location

After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in ~/.ssh/id_rsa (on Linux or macOS) or in C:\Users\YourUserName\.ssh\id_rsa (on Windows).Press Enter to accept the default location, or specify a different file path.

4. Set a Passphrase (Optional)

You will be prompted to enter a passphrase. This is an additional layer of security; you can leave it blank if you don't want to use a passphrase, but using one is recommended.

5. Add the SSH Key to the SSH Agent

Start the SSH agent in the background:bashCopy codeeval "$(ssh-agent -s)" Add your SSH private key to the agent:bashCopy codessh-add ~/.ssh/id_rsa

6. Copy the SSH Public Key

To add your SSH key to a service like GitHub, GitLab, or a server, you'll need to copy the public key:bashCopy codecat ~/.ssh/id_rsa.pub Copy the output of this command, which starts with ssh-rsa, and add it to the desired service.

7. Testing the SSH Connection

p

Last 30 days

Formula

PROCEDURE GetTopResults

CurrencyReference Table

Python Dash App: Calculate Difference

PowerShell Function to get the last business day from today's date

Grovey

Step 4: Configure the Service Using NSSM GUI

Step-by-Step Guide for Offline Installation of python packages

Step 1: Download All Required Packages and Dependencies

Download Packages on an Online Machine:shCopy codemkdir dash_packages pip download -d dash_packages dash This command downloads the dash package and all its dependencies into the dash_packages directory.

Step 2: Transfer Packages to the Offline Environment

Transfer the dash_packages Directory: Use a USB drive, external hard drive, or any other method to copy the dash_packages directory to the offline environment.

Step 3: Install Packages in the Offline Environment

Install Packages Using pip:shCopy codepip install --no-index --find-links=dash_packages dash This command tells pip to install the dash package and its dependencies from the local dash_packages directory instead of looking for them online.

Handling Complex Dependencies

Alternative: Using pip wheel

Example: Downloading and Installing Dash with Dependencies

Verification and Troubleshooting

Conclusion

Power BI Interview Questions and Answers

Basic Python interview questions - Set 1 - 55 Questions and Answers

R - For Loop API upload - Key

API - Reed.co.uk - R Code - Key Req

API - Give Food - No key - R code

test

ISIN Validation SQL Function

xml example

XML row count using python

Migrating SQL Server Reporting Services (SSRS) from a busy server to a dedicated server can provide several benefits

Python code to unprotect the excel sheet with a valid password

If you're on Linux or macOS, you can use the built-in terminal.
On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed.

After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in `~/.ssh/id_rsa` (on Linux or macOS) or in `C:\Users\YourUserName\.ssh\id_rsa` (on Windows).
Press `Enter` to accept the default location, or specify a different file path.

Start the SSH agent in the background:
bash
`eval "$(ssh-agent -s)"`
Add your SSH private key to the agent:
bash
`ssh-add ~/.ssh/id_rsa`

To add your SSH key to a service like GitHub, GitLab, or a server, you'll need to copy the public key:
bash
`cat ~/.ssh/id_rsa.pub`
Copy the output of this command, which starts with `ssh-rsa`, and add it to the desired service.

Download Packages on an Online Machine:
sh
`mkdir dash_packages pip download -d dash_packages dash`
This command downloads the `dash` package and all its dependencies into the `dash_packages` directory.

Transfer the `dash_packages` Directory: Use a USB drive, external hard drive, or any other method to copy the `dash_packages` directory to the offline environment.

Install Packages Using `pip`:
sh
`pip install --no-index --find-links=dash_packages dash`
This command tells `pip` to install the `dash` package and its dependencies from the local `dash_packages` directory instead of looking for them online.

Alternative: Using `pip wheel`