Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table that I'm loading to Qlikview, where the data is at a Quarterly level - Jan, Apr, Jul,Oct. I want to replicate the previous month's values for Feb & Mar, May & Jun resp.
This is the script for loading the data.
Price:
LOAD [MarketPrice_id]
,[IndexName]
,[Date]
,[MarketPrice]]
,[UpdatedOn]
Where Year([Date]) > 2016;
SELECT [MarketPrice_id]
,[IndexName]
,[Date]
,[MarketPrice]]
,[UpdatedOn]
FROM [zzz].[dbo].[tblMarketPrices];
The table structure is as follows:
MarketPrice_id | IndexName | Date | MarketPrice | UpdatedOn |
40001 | A | 1/1/2017 | 2 | 11/7/16 |
40002 | A | 4/1/2017 | 5 | 11/7/16 |
40003 | B | 1/1/2017 | 4 | 11/7/16 |
40004 | B | 4/1/2017 | 6 | 11/7/16 |
40005 | C | 1/1/2017 | 9 | 11/7/16 |
40006 | C | 4/1/2017 | 10 | 11/7/16 |
So I need to insert 2 rows - one for 2/1/2017 and 3/1/2017 with the same value as 1/1/2017 for the Index Name A. Similarly we need to duplicate 4/1/2017 values for 5/1/2017 and 6/1/2017 for Index Name A
We need to do this for IndexName B and C as well. Thanks in advance!
How far do you go 6/1/2017 is a random date you picked? or is there a logic to pick 6/1/2017? Also, is your date in M/D/YYYY or D/M/YYYY format?
//Use This code
Price:
LOAD [MarketPrice_id]
,[IndexName]
,[Date]
,[MarketPrice]]
,[UpdatedOn]
Where Year([Date]) > 2016;
SELECT [MarketPrice_id]
,[IndexName]
,[Date]
,[MarketPrice]]
,[UpdatedOn]
FROM [zzz].[dbo].[tblMarketPrices];
Temp:
LOAD Distinct Date,Date as DateNew,1 as Flag Resident Price;
concatenate
LOAD Distinct Date,Addmonths(Date,1) as DateNew,1 as Flag Resident Price;
concatenate
LOAD Distinct Date,Addmonths(Date,2) as DateNew,1 as Flag Resident Price;
//Flag Field is just to avoid Auto Concatenation, maybe you don't need it
Left join(Price)
LOAD Distinct Date,DateNew Resident Temp;
Drop Table Temp;
Drop Field Date;
Rename Field DateNew to Date;
/*
Temp Table with have 3 values of DateNew for a single Date, thus when you join two table your date will multiply according to your requirements */
Every date in his query looks like a quarterstart which covers 4 out of 12 monthstarts. Maybe he needs same data for rest of the months.
The data in the table is at a quarterly level. So I only have 4 data points for an Index-Month combination.(Jan, Apr, Jul, Oct)
So for 1/6/2017 (M/D/YYYY), I need to replicate the values of 4/1/2017.
Couldn't get it to work. We will be updating the values in the database side itself, so we won't need to manipulate in Qlikview and simply load the data.