Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a dataset that contains the first 3 columns from the left: Month, [Index Name] & [Index Value]. I have to create the month columns (Jan-14 ,Aug-14 etc.). I have been able to perform these steps when there's a single value for [Index Name]. But I am not able to repeat it for > 1 values of [Index Name] and it's messing up the output. Following is the desired output:
Month | Index Name | Index Value | Jan-14 | Feb-14 | Mar-14 | Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 |
1/1/2014 | Index1 | 110 | 1.00 | |||||||
2/1/2014 | Index1 | 105 | 0.95 | 1 | ||||||
3/1/2014 | Index1 | 120 | 1.09 | 1.142857 | 1 | |||||
4/1/2014 | Index1 | 115 | 1.05 | 1.095238 | 0.958333 | 1 | ||||
5/1/2014 | Index1 | 130 | 1.18 | 1.238095 | 1.083333 | 1.130435 | 1 | |||
1/1/2014 | Index2 | 200 | 1.00 | |||||||
2/1/2014 | Index2 | 120 | 0.60 | 1 | ||||||
3/1/2014 | Index2 | 110 | 0.55 | 0.916667 | 1 | |||||
4/1/2014 | Index2 | 100 | 0.50 | 0.833333 | 0.909091 | 1 | ||||
5/1/2014 | Index2 | 113 | 0.57 | 0.941667 | 1.027273 | 1.13 | 1 | |||
6/1/2014 | Index2 | 114 | 0.57 | 0.95 | 1.036364 | 1.14 | 1.00885 | 1 | ||
7/1/2014 | Index2 | 110 | 0.55 | 0.916667 | 1 | 1.1 | 0.973451 | 0.964912 | 1 | |
8/1/2014 | Index2 | 120 | 0.60 | 1 | 1.090909 | 1.2 | 1.061947 | 1.052632 | 1.090909 | 1 |
I have attached my QVW, and an excel file with the input data in grey columns and output in yellow/green columns; I would really appreciate it if you can help me figure it out. Thanks!
-MalvikaSohaney
Find the below script: It automatically create if you have new month fields
Source:
LOAD * Inline [
Month,IndexName,IndexValue
41640,Index1,110
41671,Index1,105
41699,Index1,120
41730,Index1,115
41760,Index1,130
41791,Index1,102
41821,Index1,114
41852,Index1,117
41883,Index1,112
41913,Index1,105
41944,Index1,150
41974,Index1,120
42005,Index1,112
42036,Index1,145
42064,Index1,130
42095,Index1,125
41640,Index2,200
41671,Index2,120
41699,Index2,110
41730,Index2,100
41760,Index2,113
41791,Index2,114
41821,Index2,110
41852,Index2,120
41883,Index2,130
41913,Index2,105
41944,Index2,100
41974,Index2,130
];
Left Join (Source)
Load
IndexName ,
Month AS EachMonth,
IndexValue AS EachValue
Resident Source;
Temp:
LOAD
Month,
IndexName,
IndexValue,
EachMonth,
EachValue,
Replace(MonthName(Month),' ','_') AS NewMonthName ,
IF(EachMonth >= Month, Num(EachValue/IndexValue,'###0.00') , Null()) AS FinalOP
Resident Source
;
DROP Table Source ;
LET vTableName = 'Final:' ;
FOR i=1 to FieldValueCount('NewMonthName')
LET vMonthValue = FieldValue('NewMonthName',$(i));
TRACE Month Value --> $(vMonthValue);
$(vTableName)
LOAD
EachMonth AS Month,
IndexName,
EachValue as IndexValue,
FinalOP AS $(vMonthValue)
Resident Temp
Where NewMonthName = '$(vMonthValue)'
;
LET vTableName = 'Join(Final)';
LET vMonthValue = ;
NEXT i
DROP Table Temp;
I am also attaching qvw file as reference.
You want the column in the script or front end chart?
May be this:
Table:
LOAD Month,
[Index Name],
[Index Value]
FROM
Mark_Index_Test_2Indices.xlsx
(ooxml, embedded labels, table is Sheet1);
Join(Table)
LOAD *,
[Index Value]/Peek as [Jan-14],
[Index Value]/Peek2 as [Feb-14],
[Index Value]/Peek3 as [Mar-14],
[Index Value]/Peek4 as [Apr-14];
LOAD *,
If(Peek('Index Name') = [Index Name], Peek('Peek'), [Index Value]) as Peek,
If(Peek('Index Name', -2) = [Index Name], Peek('Peek2'), If(Peek('Index Name') = [Index Name], [Index Value])) as Peek2,
If(Peek('Index Name', -3) = [Index Name], Peek('Peek3'), If(Peek('Index Name', -2) = [Index Name], [Index Value])) as Peek3,
If(Peek('Index Name', -4) = [Index Name], Peek('Peek4'), If(Peek('Index Name', -3) = [Index Name], [Index Value])) as Peek4
Resident Table
Order By [Index Name], Month;
Output:
Find the below script: It automatically create if you have new month fields
Source:
LOAD * Inline [
Month,IndexName,IndexValue
41640,Index1,110
41671,Index1,105
41699,Index1,120
41730,Index1,115
41760,Index1,130
41791,Index1,102
41821,Index1,114
41852,Index1,117
41883,Index1,112
41913,Index1,105
41944,Index1,150
41974,Index1,120
42005,Index1,112
42036,Index1,145
42064,Index1,130
42095,Index1,125
41640,Index2,200
41671,Index2,120
41699,Index2,110
41730,Index2,100
41760,Index2,113
41791,Index2,114
41821,Index2,110
41852,Index2,120
41883,Index2,130
41913,Index2,105
41944,Index2,100
41974,Index2,130
];
Left Join (Source)
Load
IndexName ,
Month AS EachMonth,
IndexValue AS EachValue
Resident Source;
Temp:
LOAD
Month,
IndexName,
IndexValue,
EachMonth,
EachValue,
Replace(MonthName(Month),' ','_') AS NewMonthName ,
IF(EachMonth >= Month, Num(EachValue/IndexValue,'###0.00') , Null()) AS FinalOP
Resident Source
;
DROP Table Source ;
LET vTableName = 'Final:' ;
FOR i=1 to FieldValueCount('NewMonthName')
LET vMonthValue = FieldValue('NewMonthName',$(i));
TRACE Month Value --> $(vMonthValue);
$(vTableName)
LOAD
EachMonth AS Month,
IndexName,
EachValue as IndexValue,
FinalOP AS $(vMonthValue)
Resident Temp
Where NewMonthName = '$(vMonthValue)'
;
LET vTableName = 'Join(Final)';
LET vMonthValue = ;
NEXT i
DROP Table Temp;
I am also attaching qvw file as reference.
Thanks a lot dathu.qv! This solves the problem.
Your welcome and It take a lot of time to get solution.
Yeah, it's a really tricky problem, I had been stuck at it for a long time. Again, thanks a lot, this solved a big issue.
I was trying to follow your logic towards the end of the script. It is a very different approach, can you share a brief description of your logic. Thank you!