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
