Using Macro in Notepad++

 Sometimes, you need to bulk-edit text with the same patterns. However, you can't simply use text editor to find and replace the content. In this case, you can try using macro in Notepad++ to achieve that.

This is a simple video on how you can do that using Notepad++:

Read More

Split Comma-Delimited Column into Multiple Rows

We had post about combining multiple rows into a column in the past. Sometimes, on the contrary, we would want to split character-delimited column into multiple rows. In this post, we will see try to split comma-delimited column into multiple rows. Sample Data:

End result after split into rows:

Query:

-- Create temporary table to store UserGroup
create table #UserGroup (GroupID int, UserGroup nvarchar(1000), Users nvarchar(1000))
insert into #UserGroup values (1, 'Group 1', 'Himura,Selvi,Superman')
INSERT INTO #UserGroup VALUES ( 2, 'Group 2', 'Luck,Smarty')
INSERT INTO #UserGroup VALUES ( 3, 'Group 3', 'Lucky')

--Display the sample data
select * from #UserGroup

--Split comma-delimited column into multiple rows
;WITH tmp(GroupID, UserGroup, Username, Users) AS
(
	SELECT GroupID, 
		UserGroup,
		convert(nvarchar(1000), LEFT(Users, CHARINDEX(',', Users + ',') - 1)),
		convert(nvarchar(1000), STUFF(Users, 1, CHARINDEX(',', Users + ','), ''))
	FROM #UserGroup
	where Users is not null
	UNION all

	SELECT GroupID, 
		UserGroup,
		convert(nvarchar(1000), LEFT(Users, CHARINDEX(',', Users + ',') - 1)),
		convert(nvarchar(1000), STUFF(Users, 1, CHARINDEX(',', Users + ','), ''))
	FROM tmp
	WHERE
		Users > ''
)
select GroupID, UserGroup, Username
from tmp
order by GroupID, UserGroup

Read More

Using SQL Cursor for Looping

If you ever heard of SQL cursor, this is probably not a new thing to you. If you never heard or use cursor in SQL before, we usually use cursor to replace the ordinary looping in SQL. The performance is generally better compared to using WHILE or ordinary looping method, depending on how it is implemented. Reason being, cursor is stored in memory.

I have a good example on usage of cursor in SQL.
In CRM & loyalty, we are looking at membership and loyalty points. Customers earn points when they transact or purchase something.
In this scenario, there is a points cap for members transactions, in which customers can only earn x number of points from their purchase.

We want to retrospectively calculate the correct points earned given customer's transactions and the points they earned.

Example of the records:

Points is capped at member-level. This means, different customer / member can have different points cap, denoted in the last column in the example above.
Since we are looking at member-level, we will look at the re-arranged version of the records.

This is the end result we are looking at. Observe the last field in the table. The content in the Correct_PointsEarned is always within the points cap for each member, in the order of the transaction sequence. Once the points cap has been met, the Correct_PointsEarned will be 0 for that member.

You should familiarize yourself with how to create a cursor first.
This is a template of how a cursor looks like:

--START CURSOR
declare cur1 cursor for
select *
from 
order by 

open cur1

fetch next from cur1
into @zzz, @sss, ......

while @@FETCH_STATUS = 0
BEGIN
 --do the necessary processing here
 
 
 --fetch next record
 fetch next from cur1
 into @zzz, @sss, ......
END

close cur1
deallocate cur1
--END CURSOR

This is the script to create temp table to populate the data:

create table #TmpPointsCap (
 TransactAutoID bigint identity(1,1),
 MemberID nvarchar(100),
 TransactDate date,
 TransactPoints numeric(18,4),
 PointsCap numeric(18,4),
 Correct_PointsEarned numeric(18,4) default(0.00),
 AddedOn datetime default(getdate())
)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/01', 100, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/02', 200, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0004', '2019/01/02', 400, 300)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/03', 500, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/03', 700, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/05', 200, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/06', 100, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/06', 300, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/06', 400, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/08', 200, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/08', 100, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0004', '2019/01/09', 100, 300)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0005', '2019/01/10', 200, 200)

--Original table content
select * from #Tmppointscap

--The data sorted in the order we want
select TransactAutoID, MemberID, TransactDate, TransactPoints, PointsCap, Correct_PointsEarned = 0
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

And this is how we use cursor to achieve the correct point earned in the last column:

--Temporary variables to store the table fields
declare @MemberID nvarchar(100), @TransactAutoID bigint, @TransactPoints numeric(18,4), @PointsCap numeric(18,4)
declare @PrevMemberID nvarchar(100) = ''
declare @RunningPoints numeric(18,4) = 0


--START CURSOR
declare cur1 cursor for
select MemberID, TransactAutoID, TransactPoints, PointsCap
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

open cur1

fetch next from cur1
into @MemberID, @TransactAutoID, @TransactPoints, @PointsCap

while @@FETCH_STATUS = 0
BEGIN
 --do the necessary processing here
 if @MemberID <> @PrevMemberID
 begin
  --set initial running points as the 1st TransactPoints
  set @RunningPoints = 0
 end
 
 if @RunningPoints < @PointsCap and (@RunningPoints + @TransactPoints) > @PointsCap
 begin
  --if after adding the TransactPoints, it is bigger than the pointscap
  --only earn at most, as much as the pointscap
  update #TmpPointsCap
  set Correct_PointsEarned = @PointsCap - @RunningPoints
  where TransactAutoID = @TransactAutoID

  --Update RunningPoints = PointsCap
  set @RunningPoints += @TransactPoints
 end
 else if (@RunningPoints + @TransactPoints) = @PointsCap
 begin
  --earning all the transactpoints just good
  update #TmpPointsCap
  set Correct_PointsEarned = @TransactPoints
  where TransactAutoID = @TransactAutoID
  
  set @RunningPoints += @TransactPoints
 end
 else if @RunningPoints < @PointsCap
 begin
  --update the running points
  set @RunningPoints += @TransactPoints
  
  --update the correct points earned in the table
  update #TmpPointsCap
  set Correct_PointsEarned = TransactPoints
  where TransactAutoID = @TransactAutoID
 end
 else
 begin
  --not earning any points anymore
  update #TmpPointsCap
  set Correct_PointsEarned = 0
  where TransactAutoID = @TransactAutoID
 end
 
 set @PrevMemberID = @MemberID

 --fetch next record
 fetch next from cur1
 into @MemberID, @TransactAutoID, @TransactPoints, @PointsCap
END

close cur1
deallocate cur1
--END CURSOR

--Check the temp table again after updated
select TransactAutoID, MemberID, TransactDate, TransactPoints, PointsCap, Correct_PointsEarned
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

Good Luck, and Happy Query-ing!

Read More

Connecting to SQL Instance with Non-Default Port on Pentaho

If you are trying to connect to SQL instance on Pentaho, and your SQL instance is using non-default port, then you need to fill in the connection string a bit differently.

This is how we normally fill in the connection to SQL instance, when connecting to default port.

If you are connecting to some other ports, you need to explicitly type in the connection parameters inside the IP field.

Read More

Dual Axis not Working in Tableau

Problem:
When creating chart with Dual Axis, Synchronize Axis is disabled and cannot be clicked.

Cause:
Tableau identified this as a bug, though it is supposed to have been fixed on later version, but it somehow still happens.

Solution:
1. To enable Synchronize Axis, make sure that the data type of both the measures are the same.

2. If you are using Integer data type, problem persists even if the data types are the same. You should convert the measures to float.
Notice that now the Synchronize Axis is enabled after we convert the data type to float.

Format it with 0 decimal points if you want it to appear as integer.

And this is how the end result looks like.

PS: I know the graph is ugly, but I hope you get what you need :)

Read More

Tableau Server Unable to Start due to Blocked Ports

Problem:
Sometimes you might need to use different (from the default port) port on Tableau. By default, SSL is using port 443. For my case, I use SSL and port 8443 for Tableau Server. After you changed the port, and tried to start Tableau service, you get this error:
*** Tableau Server Gateway requires port 443,
***    in use by process 4.
*** Tableau Server unable to start due to blocked ports.

Solution:
There are 2 files you need to change:
1. C:\Tableau\Tableau Server\data\tabsvc\config\httpd.conf
2. C:\Program Files\Tableau\Tableau Server\10.3\apache\conf\original\extra\httpd-ssl.conf

Change the port to listen to 8443 (replace 443 to 8443) on these 2 files.

Read More

Frame cannot Load - Content Security Policy Issue

Problem:
You are using iframe to load a site from within a website, and the frame does not load.
In my case, I am using https for both the website and the iframe.

Error:
If you press F12 on the browser, you will see error similar to:

Refused to frame 'https://iframe_site' because it violates the following Content Security Policy directive: default-src 'self' 'unsafe-inline' 'unsafe-eval'...... (this can be found in web.config). Note that 'script-src' was not explicitly set, so 'default-src' is used as a fallback.

Solution:
Add the iframe_site to content-security-policy on the web.config.

<httpProtocol>
 <customHeaders>
  <add name="Content-Security-Policy" value="default-src 'self' 'unsafe-inline' 'unsafe-eval' iframe_site;" />
 </customHeaders>
</httpProtocol>

PS: If you are using port for the iframe site, you would need to include the port when adding into web.config.

Read More

Cisco VPN not Working on Windows 10

Error:
Cisco VPN is not working (cannot login and connect to VPN connection).

Solution:
1. Right-click on the VPN client, and Run as Administrator.
2. Open regedit and go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA and rename:

• x86 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter" to "Cisco Systems VPN Adapter"
• x64 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows" to "Cisco Systems VPN Adapter for 64-bit Windows"

Source partially from: IT that should just work

Read More

Find Maximum Value from Multiple Columns

A colleague asked about how to get maximum value from multiple columns. After searching it online, I found a pretty good way to get it, and thought it would be good to share here.

For example, I have a table with MemberID and 3 date fields. I want to get the last date from the 3 date fields for each MemberID.

The expected end result look like:

Creating the sample data:

create table #member ( MemberID nvarchar(100), modifiedon datetime, deletedon datetime, addedon datetime )
insert into #member values ('M1', GETDATE(), GETDATE(), GETDATE())
insert into #member values ('M2', GETDATE()-1, GETDATE()-2, GETDATE()-3)
insert into #member values ('M3', GETDATE()-3, GETDATE()-2, GETDATE()-1)

Query to get the max for multiple columns:

SELECT 
 MemberID, 
 LastUpdateDate =
 ( SELECT MAX(LastUpdateDate)
  FROM (VALUES (modifiedon),(deletedon),(addedon)) AS UpdateDate(LastUpdateDate)
 )
FROM #member

Read More

Calculating Customer's Latency

There were times when I was asked to retrieve the latency of the customers in my work. At first, I was confused about how to retrieve this. After some thought, I find a solution, which is actually pretty simple, and the SQL usage is widely available. Unfortunately, when I tried searching it online, the SQL technique is seldom associated with latency problem in retail industry.

This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.

To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.

Here are the SQL queries to prepare the data:

--Create temp table
CREATE TABLE #Transact
(
 MemberID NVARCHAR(100),
 RecieptNo NVARCHAR(100),
 TransactDate DATE
)

--Insert dummy data
INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20')
INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30')
INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05')

INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12')
INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15')
INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25')
INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')

Here are the SQL queries to get the Latency per member:

--Store the ranked transaction into temp table. Alternately, use common table expression
SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate)
INTO #1
FROM #Transact

--Get the average latency per member
SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency))
FROM ( SELECT a.MemberID,
   Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate)
  FROM #1 a
  INNER JOIN #1 b
   ON b.MemberID = a.MemberID
   AND b.RankNo = (a.RankNo + 1) 
) aa GROUP BY aa.MemberID

That's all folks!

Read More

A Severe Error Occurred on The Current Command

SQL Error:
A severe error occurred on the current command. The results, if any, should be discarded.

Solution:
Disconnect and connect to the SQL database again.

Source: Stackoverflow

Read More