topic Re: Loading data only for recent 3 years in QlikView

Loading data only for recent 3 years

Sun, 28 Jun 2015 01:28:44 GMT

hello friends, i got a issue while loading data from master calendar..

i need to load Date recent 3 three years..with filling missing dates..

so far i got this result...pls check...

i got all values ...even i got missing dates also..!! need to change date format in script..

2) When i load that only for recent 3 years...iam missing dates..please someone help me to filling values and setting variable for year for recent year...

thanks

venky

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 02:28:33 GMT

From what I see, you are restricting only you master calendar to be include data from 2009 and forward, but the fact table may still have those have that date. You can restrict your fact table by using a Right Keep. Try this:

MasterCalendar:

Right Keep (FactTable) //Assuming that the fact table is loaded before Master Calendar

LOAD OrderDate,

Week,

Year,

Month,

MonthNo,

DayofYear,

MonthYear,

YearMonth,

Day,

Quarter,

YearWeek,

WeekDay,

WeekDayNo,

WeekInMonth,

CurYTDFlag,

LastYTDFlag,

CurMTDFlag,

LastMTDFlag

FROM

(qvd)

Where Year >= 2009;

See if this helps

Best,

Sunny

Re: Loading data only for recent 3 years

Sun, 28 Jun 2015 02:39:26 GMT

Thanks bro..

how to change to time format?

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 02:42:49 GMT

You want to change it from MM/DD/YYYY hh:mm:ss TT to MM/DD/YYYY????

Try this:

Date(Floor(Date#(YourDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as YourDate

Re: Loading data only for recent 3 years

Sun, 28 Jun 2015 02:48:26 GMT

thanks bro

how to allocate variable to Max(Year)...and call this variable in where clause?

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 02:51:51 GMT

Try this:

Temp:

LOAD Max(Year) as maxYear

Resident YourMasterCalendarTableName;

LET vMaxYear = Num(Peek('maxYear', 0, 'Temp'));

DROP Table Temp;

Now you can use $(vMaxYear) in the Where clause.

HTH

Best,

Sunny

Re: Loading data only for recent 3 years

Sun, 28 Jun 2015 03:03:31 GMT

where should i keep this syntax..?

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 03:17:39 GMT

‌it needs to be after the table from which you need the max year information from and before the table where you would be restricting the date. I would need to see your complete script to give you a better suggestion.

Best,

Sunny

Re: Loading data only for recent 3 years

Sun, 28 Jun 2015 03:20:24 GMT

i got it bro... thanks alot

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 03:34:45 GMT

‌No problem bud ... I am glad I was able to help.

Re: Loading data only for recent 3 years

Sun, 28 Jun 2015 18:41:05 GMT

Bro...When i used

Date(Floor(Date#(YourDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as YourDate

Only Customer Table data is visible...No data is loading from other tables..

sunindia

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 18:44:03 GMT

‌not sure what you mean? Would you be able to share your script?

Re: Loading data only for recent 3 years

Sun, 28 Jun 2015 18:50:16 GMT

Orders:

LOAD OrderID,

CustomerID,

ShipperID,

EmployeeID,

Freight,

Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate

FROM

[$(vQvdPath)Orders.qvd]

(qvd);

OrderDetails:

LOAD ProductID,

UnitPrice,

OrderID,

Quantity,

Discount

FROM

[$(vQvdPath)OrderDetails.qvd]

(qvd);

Categories:

LOAD CategoryID,

CategoryName,

Description

FROM

[$(vQvdPath)Categories.qvd]

(qvd);

Customers:

LOAD CustomerID,

CompanyName,

ContactName,

Address,

City,

StateProvince,

PostalCode,

Country,

Phone,

Fax

FROM

[$(vQvdPath)Customers.qvd]

(qvd);

Employee:

LOAD EmployeeID,

[Last Name],

[First Name],

Title,

[Hire Date],

Office,

Extension,

[Reports To],

[Year Salary]

FROM

[$(vQvdPath)Employee.qvd]

(qvd);

Office:

LOAD Office,

OfficeAddress,

OfficePostalCode,

OfficeCity,

OfficeStateProvince,

OfficePhone,

OfficeFax,

OfficeCountry

FROM

[$(vQvdPath)Office.qvd]

(qvd);

Products:

LOAD ProductID,

ProductName,

SupplierID,

CategoryID,

QuantityPerUnit,

UnitCost,

Prod_UnitPrice,

UnitsInStock,

UnitsOnOrder

FROM

[$(vQvdPath)Products.qvd]

(qvd);

Shippers:

LOAD ShipperID,

Shipper_CompanyName

FROM

[$(vQvdPath)Shippers.qvd]

(qvd);

MasterCalendar:

Right Keep (Orders)

LOAD

OrderDate,

Week,

Year,

Month,

MonthNo,

DayofYear,

MonthYear,

YearMonth,

Day,

Quarter,

YearWeek,

WeekDay,

WeekDayNo,

WeekInMonth,

CurYTDFlag,

LastYTDFlag,

CurMTDFlag,

LastMTDFlag

FROM

[$(vQvdPath)MasterCalendar.qvd]

(qvd) Where Year>=($(vMaxYear)-2);

Temp:

LOAD Max(Year) as maxYear

Resident MasterCalendar;

LET vMaxYear = Num(Peek('maxYear', 0, 'Temp'));

DROP Table Temp;

Re: Loading data only for recent 3 years

sunny_talwar — Sun, 28 Jun 2015 20:48:38 GMT

I can see why Orders table may be dropped completely, but not sure why the other tables are having trouble loading in. Do you not get the same problem if you don't use Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate and instead just use OrderDate without any conditions? I suggest running the script and generate logfile and use logfile to troubleshoot as well.

I am made few changes to the script, see if those changes help:

Orders:

LOAD OrderID,

CustomerID,

ShipperID,

EmployeeID,

Freight,

Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate

FROM

[$(vQvdPath)Orders.qvd]

(qvd);

OrderDetails:

LOAD ProductID,

UnitPrice,

OrderID,

Quantity,

Discount

FROM

[$(vQvdPath)OrderDetails.qvd]

(qvd);

Categories:

LOAD CategoryID,

CategoryName,

Description

FROM

[$(vQvdPath)Categories.qvd]

(qvd);

Customers:

LOAD CustomerID,

CompanyName,

ContactName,

Address,

City,

StateProvince,

PostalCode,

Country,

Phone,

Fax

FROM

[$(vQvdPath)Customers.qvd]

(qvd);

Employee:

LOAD EmployeeID,

[Last Name],

[First Name],

Title,

[Hire Date],

Office,

Extension,

[Reports To],

[Year Salary]

FROM

[$(vQvdPath)Employee.qvd]

(qvd);

Office:

LOAD Office,

OfficeAddress,

OfficePostalCode,

OfficeCity,

OfficeStateProvince,

OfficePhone,

OfficeFax,

OfficeCountry

FROM

[$(vQvdPath)Office.qvd]

(qvd);

Products:

LOAD ProductID,

ProductName,

SupplierID,

CategoryID,

QuantityPerUnit,

UnitCost,

Prod_UnitPrice,

UnitsInStock,

UnitsOnOrder

FROM

[$(vQvdPath)Products.qvd]

(qvd);

Shippers:

LOAD ShipperID,

Shipper_CompanyName

FROM

[$(vQvdPath)Shippers.qvd]

(qvd);

TempMasterCalendar:

LOAD

OrderDate,

Year

FROM

[$(vQvdPath)MasterCalendar.qvd]

(qvd) Where Year>=($(vMaxYear)-2);

Temp:

LOAD Max(Year) as maxYear

Resident MasterCalendar;

LET vMaxYear = Num(Peek('maxYear', 0, 'Temp'));

DROP Tables Temp, TempMasterCalendar;

MasterCalendar:

Right Keep (Orders)

LOAD

Date(Floor(Date#(OrderDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as OrderDate,

Week,

Year,

Month,

MonthNo,

DayofYear,

MonthYear,

YearMonth,

Day,

Quarter,

YearWeek,

WeekDay,

WeekDayNo,

WeekInMonth,

CurYTDFlag,

LastYTDFlag,

CurMTDFlag,

LastMTDFlag

FROM

[$(vQvdPath)MasterCalendar.qvd]

(qvd) Where Year>=($(vMaxYear)-2);