Select In Field action In qlikview

We have option select in field in qlikview . We can use this option to select one more fields having same values.

1) Selecting single value
2)Selecting Multiple value in a field
3)Selecting multiple values with spaces in a field .

1)   Selecting single value : Is straight forward  select a field and pass value with single quotes .

2)Selecting Multiple value in a field.
We have to pass values with in brackets ( ) and a separation (example : | )

After clear it selects all the values  to avoid this we use  trick like below .

3)Selecting multiple values with spaces in a field .

YTD in qlikview

=sum({<Year={$(=Max(Year))},MonthNUmber={"<=$(=max(MonthNUmber))"},OrderDate={"<=$(=max(OrderDate))"},Quarter=,Month=>}UnitPrice) 

Incremental loading in qlikview

//Qvd_Load:
//LOAD Id,
//    Name,
//    date;
//SQL SELECT *
//FROM "Product_master".dbo.Test;
//
//Store Qvd_Load into C:\mahantesh\QlikView\Nuvento\tests\SQL_Test.qvd(qvd);
//
//drop table Qvd_Load;

Qvd_Load1:
LOAD Id,
    Name,
    date ;
SQL SELECT *
FROM "Product_master".dbo.Test;

Latest_Date:
LOAD date Resident Qvd_Load1 order by date desc ;

let vdate=Peek('date',0,'Latest_Date');

drop table Qvd_Load1;
drop Table Latest_Date;

Increment_load:
//LOAD * where date > $(vdate);
LOAD Id,
    Name,
    date;
SQL SELECT *
FROM "Product_master".dbo.Test
where date >'$(vdate)';

Concatenate
LOAD Id,
     Name,
     date
FROM
C:\mahantesh\QlikView\Nuvento\tests\SQL_Test.qvd
(qvd)
where not Exists(Id);


inner join(Increment_load)
LOAD Id,
    Name,
    date;
SQL SELECT *
FROM "Product_master".dbo.Test;


STORE Increment_load into C:\mahantesh\QlikView\Nuvento\tests\SQL_Test.qvd(qvd);

DROP Table Increment_load;


LOAD Id,
     Name,
     date
FROM
C:\mahantesh\QlikView\Nuvento\tests\SQL_Test.qvd
(qvd);

Audit package details in using Stored procedure

Where [dbo].[PDV_Insert_PDV_Audit_Package] =Name of the SP

      PDV_Audit_Package is Table name where we are storing audit details.

Create Procedure [dbo].[PDV_Insert_PDV_Audit_Package]

(

      @ParentPackageID UniqueIdentifier, 

      @ParentPackageName NVarchar(50),   

      @PackageID UniqueIdentifier,

      @PackageName NVarchar(50),

      @Host NVarchar(50),

      @ExecutionStatus Varchar(20),

      @ExecutionID Varchar(14)

) As

Begin

      Set NoCount On

     

      Insert PDV_Audit_Package

            (

                  ParentPackageID,

                  ParentPackageName,

                  PackageID,

                  PackageName,

                  Host,

                  ExecutionStatus,

                  ExecutionID,

                  PackageExecutionTime

            )

      Values

            (

                  @ParentPackageID,

                  @ParentPackageName,

                  @PackageID,

                  @PackageName,

                  @Host,

                  @ExecutionStatus,

                  @ExecutionID,

                  GETDATE())

End

substract string from column in sql

SELECT eventname
 ,CASE 
  WHEN charindex('vs', eventname) > 0
   THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname) + 3, len(eventname)))
  END Away_Team
 ,replace(replace(eventname, CASE 
    WHEN charindex('vs', eventname) > 0
     THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname), len(eventname)))
    END, ''), SUBSTRING(replace(eventname, CASE 
     WHEN charindex('vs', eventname) > 0
      THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname), len(eventname)))
     END, ''), 0, PatIndex('%[0-9]%', replace(eventname, CASE 
      WHEN charindex('vs', eventname) > 0
       THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname), len(eventname)))
      END, '')) + 2), '') Home_Team
--SUBSTRING(replace(eventname,case when charindex('vs',eventname)>0 then 
--rtrim(SUBSTRING(eventname,charindex('vs',eventname),len(eventname))) end,''),0,PatIndex('%[0-9]%',
--replace(eventname,case when charindex('vs',eventname)>0 then 
--rtrim(SUBSTRING(eventname,charindex('vs',eventname),len(eventname))) end,''))+2)
FROM tablename

where eventname  is a column name

Year Month code starts from November

To get  Year month code column to starts add below query in filter .
[Monthcode Column] > (case when right(?Period?,2) in ('11','12')
 then left(?Period?,4)+'10'
else
cast(left(?Period?,4)-1,varchar(4))+'10'
end)
and
[Month code]<=?Period?

where ?Period? is parameter.

Execute process task in ssis

Executable : C:\Program Files\7-Zip\7z.exe

Argument : example
e D:\Employees.7z -oD:\mahantesh

Source file path:D:\Employees.7z
Output path: D:\mahantesh
Expression:  "a "+ "\""+ @[User::zip_destFilepath]+ @[User::zip_outputfilename]+ "\""+" "+"\""+ @[User::zip_sourcefilepath]+ @[User::Filename]+"\""