Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i want create a monthly based qvd what would be the script?
Sales:
Load *, Month(Date) as Month, Year(Date) as Year Inline
[
Customer, Date, Sales
D, 15/05/2012, 110
A, 01/01/2013, 100
B, 15/02/2013, 120
C, 25/03/2013, 200
A, 15/01/2014, 120
B, 25/02/2014, 140
C, 30/03/2014, 300
];
Temp:
Load Distinct Month as Temp_Month Resident Sales;
Temp2:
Load Distinct Year as Temp_Year Resident Sales;
Let vMonth = FieldValueCount('Temp_Month');
Let vYear = FieldValueCount('Temp_Year');
For i = 0 to $(vYear)-1
Let vYearValue = Peek('Temp_Year',$(i),'Temp2');
For j = 1 to $(vMonth)-1
Let vMonthValue = Peek('Temp_Month',$(j),'Temp');
Trace << '$(vYearValue)'-'$(vMonthValue)';
TempSales:
Load Customer, Date, Sales
Resident Sales
Where Year = '$(vYearValue)' and Month = '$(vMonthValue)';
Store TempSales into Sales_$(vYearValue)$(vMonthValue);
Trace << Stored $(vYearValue)$(vMonthValue);
Drop Table TempSales;
Next
Next
Drop Tables Temp,Temp2;
Based on this little info it is hard to tell what you exactly need.
Assuming you have 1 fact table that you wish to store by month you need a loop (for each) to limit your data that will be stored in a qvd.
S EAXCTLY
I WANT SCRIPT FOR THAT
1)If you have date field like 11/12/2014
get month,Date and Year out of it
2)Store months into variable in for loop
3)in the same loop you can store it into qvd using the variable
Sales:
Load *, Month(Date) as Month, Year(Date) as Year Inline
[
Customer, Date, Sales
D, 15/05/2012, 110
A, 01/01/2013, 100
B, 15/02/2013, 120
C, 25/03/2013, 200
A, 15/01/2014, 120
B, 25/02/2014, 140
C, 30/03/2014, 300
];
Temp:
Load Distinct Month as Temp_Month Resident Sales;
Temp2:
Load Distinct Year as Temp_Year Resident Sales;
Let vMonth = FieldValueCount('Temp_Month');
Let vYear = FieldValueCount('Temp_Year');
For i = 0 to $(vYear)-1
Let vYearValue = Peek('Temp_Year',$(i),'Temp2');
For j = 1 to $(vMonth)-1
Let vMonthValue = Peek('Temp_Month',$(j),'Temp');
Trace << '$(vYearValue)'-'$(vMonthValue)';
TempSales:
Load Customer, Date, Sales
Resident Sales
Where Year = '$(vYearValue)' and Month = '$(vMonthValue)';
Store TempSales into Sales_$(vYearValue)$(vMonthValue);
Trace << Stored $(vYearValue)$(vMonthValue);
Drop Table TempSales;
Next
Next
Drop Tables Temp,Temp2;
Can you provide details how your fact table is constructed. We can help you better then.
s exactly i have field like
date:
11/12/2014
from this tell me how to store separate qvd for every month pls
Tried the script I have given.. This will create QVD for different YearMonth