December 10, 2017

Downgrade Tableau workbook from latest version to older version of tableau

Example: from version 10.0.1 to tableau 9.3

From version 10.0.1 to tableau 9.1

Step 1: 
We need to create twb file, if it is already a twb file the no need to make any change. If you have twbx then Unpackage the workbook by right click on Workbook and Select Unpackaged if workbook is packaged.

Step 2
               Next step is to open twb file in Notepad.
               Select the file and right click on the twb file and select open with notepad

Step 3
               In notepad file Find the latest version i.e. 10.0.0 and replace with the version you want to be in
Example if you have tableau 10.0.0 and you want to convert into tableau 9.3 then find 10.0 in the notepad and replace with 9.3
There should be at least 3 instances where you have to replace the version in notepad.

Step 4
               Save the notepad file as it is.

Step 5
               You are done, now you can again right click on the twb file and select package workbook

June 17, 2017

file extensions available in Tableau



Different file extensions available in Tableau


  • Tableau Workbook (.twb).
  •  Tableau Packaged Workbook (.twbx).
  • Tableau Datasource (.tds).
  • Tableau Packaged Datasource (.tdsx).
  • Tableau Data extract (.tde).
  • Tableau Bookmark (.tdm).
  • Tableau Map Source (.tms).
  • Tableau Preferences (.tps)

January 19, 2017

Get list of tables used in Stored procedure


SELECT DISTINCT        so.ID, so.name AS 'Procedure Name' , oo.name AS 'Table Name'
FROM sysdepends d, sysobjects so, sysobjects oo
WHERE    so.id=d.id
      AND so.name= 'Stored_Proc_Name'   -- Enter Your stored Procedure Name here
      AND oo.id=d.depid
ORDER BY so.NAME,oo.NAME


January 13, 2017

Find a sting in SQL table

CREATE PROCEDURE dbo.FindStringInTable @stringToFind VARCHAR(100)
AS

BEGIN TRY
 
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [dbo].[TableName] WHERE '
          
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
   
   FROM INFORMATION_SCHEMA.COLUMNS
   
   WHERE TABLE_SCHEMA = 'dbo'
   
   AND TABLE_NAME = 'TableName' -- This condition can be removed.
   
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   
   EXEC (@sqlCommand)
   
   PRINT @sqlCommand

END TRY

BEGIN CATCH
   PRINT 'Unknown Error !!'

END CATCH

 Execute FindStringInTable '[String to Search]'

August 6, 2016

SQL Date Practical Question

Question here was we need to find the maximum date from the list of rows.
Consider an example…….



with Dataset (dt1, dt2, dt3)
as  (

select Getdate() as dt1 , getdate() + 10 as dt2 , getdate() + 3 as dt3
union
select Getdate() + 5 as dt1 , getdate() -5   as dt2 , getdate() + 12 as dt3
union
select Getdate() + 10 as dt1 , getdate() -3   as dt2 , getdate() + 4 as dt3


)


SELECT * FROM Dataset



The Question is we need a maximum date in each row

We can write the query in multiple ways. But simplest way to get the output is as follows

SELECT
CASE WHEN (dt1 > dt2) and (dt1> dt3) then dt1 end,

CASE WHEN (dt2 > dt3) and (dt2> dt1) then dt2 end,

CASE WHEN (dt3 > dt1) and (dt3> dt2) then dt3 end,

dt1, dt2, dt3


FROM dataset







Add your comments or different ways to get the above output

July 9, 2016

SSIS upload a files in folder


SSIS Scenario Based Question

Consider a scenario where you have to upload 4 files into SQL server from folder daily.
Client has requirement that if there are only 3 files are available out of 4 files then also our package should execute with those 3 files and load those 3 files into server.

Provided that your file format is different for all the files and destination tables are also different.

To solve this problem we have to write a package with dynamic Enable and disable attribute of a container or data flow task

Create few variables to enable and disable the task.

In this script task we need to write a logic to read the file from the provided folder and based on the file availability we set value to variables





In the data flow task we need write logic to upload the file.

 

April 17, 2016

Group by and Having Clause

HAVING CLAUSE and a WHERE CLAUSE?


The HAVING clause is a filter that acts similar to a WHERE clause, but the filter acts on groups of rows rather than on individual rows.


In other words, the HAVING clause is used to qualify the results after the GROUP BY has been applied.
The WHERE clause, in contrast, is used to qualify the rows that are returned before the data is 

aggregated or grouped.
HAVING qualifies the aggregated data after the data has been grouped or aggregated.