Transform data using QV

Thu, 30 Jan 2014 10:23:42 GMT

Hi All,

In my QVW previously they loaded data using sql query.

I have two tables Master and test, Master table contains all the data like startdate, enddate,amount,productid....

Test data contains as follows

Term days

---- ----

12MO 365

ANNUAL 365

6MO 182

MONTHLY 30

QTRLY 90

1MONTH 30

In master table there is a field 'System' which contains A, B, C,D as values.

For value A, date_end and date_start are in number format like 734806,734441...

But for rest of values date_end and date_start are in date format like 12/11/2011....

For this reason i have to load data two times as follows

Tab1:

Select

Company,

Customerid,

Agreement type,

producttype,

orderdate,

startdate,

Type_code,

convert(datetime,[date_end]-693596) as date_end,

convert(datetime,[date_start]-693596) as date_start,

System,

CASE

WHEN DateDiff(DAY,convert(datetime, Tab1.[date_start]-693596),convert(datetime,Tab1.[date_end]-693596))-Rab2.days >= -1 THEN 'Expired'

WHEN DateDiff(DAY,convert(datetime, Tab1.[date_start]-693596),convert(datetime,Tab1.[date_end]-693596))-Tab2.days < -1 THEN 'Active'

else 'Inactive'

END AS Status

FROM [DB1].[dbo].[Master] Tab1

LEFT JOIN [Master].[dbo].[Test] Tab2 on Tab2.Term=Tab1.[Type_code]

Where System like A;

Concatenate

Select

Company,

Customerid,

Agreement type,

producttype,

orderdate,

startdate,

Type_code,

System,

enddate,

CASE

WHEN DateDiff(DAY,convert(varchar(24),Tab1.[date_start],1),convert(varchar(24),Tab1.[date_end],101))-Tab2.days >= -1 THEN 'Expired'

WHEN DateDiff(DAY,convert(varchar(24),Tab1.[date_start],1),convert(varchar(24),Tab1.[date_end],101))-Tab2.days < -1 THEN 'Active'

else 'Inactive'

END AS Status

FROM [DB1].[dbo].[Master] Tab1

LEFT JOIN [Master].[dbo].[Test] Tab2 on Tab2.Term=Tab1.[Type_code]

Where System not like A;

Now i have to load this using Qlikview, is there any function in qlikview like CASE in SQL? Is there any possibility to load data at one time??

Please anybody can give me some idea or solution on this

Thanks,

Chiru

Re: Transform data using QV

Thu, 30 Jan 2014 10:25:41 GMT

Try with IF

Re: Transform data using QV

Thu, 30 Jan 2014 11:21:21 GMT

Hi Stefano,

i used

if(System = 'A',date(date_end - 693596,'MM/DD/YYYY'),date(date_end)) as date_end,

if(System = 'A',date(date_start - 693596,'MM/DD/YYYY'),date(date_start)) as date_start

For date_start and date_end but when it comes to CASE condition i'm unable to find the solution.

Could please give me suggestion in the form of statements?

Thanks,

Chiru

Re: Transform data using QV

Thu, 30 Jan 2014 11:27:26 GMT

Can you attach your qvw?

Re: Transform data using QV

Thu, 30 Jan 2014 11:31:59 GMT

You can use the original query whit Case in first extraction (ETL1.qvw), and store all tables in qvd for Presentation.

You know this architecture?

Re: Transform data using QV

Thu, 30 Jan 2014 13:09:43 GMT

Hi Stefano,

Yes, Present i'm loading all the data into qvd then i'm loading data from qvd.

Thanks,

Chiru

topic Re: Transform data using QV in QlikView

Transform data using QV

Re: Transform data using QV

Re: Transform data using QV

Re: Transform data using QV

Re: Transform data using QV

Re: Transform data using QV