topic Insert new rows to a table in Script in QlikView

Insert new rows to a table in Script

Mon, 06 Feb 2017 16:21:09 GMT

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!

Re: Insert new rows to a table in Script

sunny_talwar — Mon, 06 Feb 2017 16:34:04 GMT

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?

Re: Insert new rows to a table in Script

shubham_singh — Mon, 06 Feb 2017 16:50:14 GMT

//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 */

Re: Insert new rows to a table in Script

shubham_singh — Mon, 06 Feb 2017 16:52:33 GMT

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.

Re: Insert new rows to a table in Script

Mon, 06 Feb 2017 17:14:01 GMT

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.

Re: Insert new rows to a table in Script

Tue, 21 Feb 2017 08:21:59 GMT

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.