creating new fields in script

[#1]

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

[sunny_talwar]

You want the column in the script or front end chart?

[sunny_talwar]

[sunny_talwar]

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:

[#5 · Accepted solution]

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.

[#6]

Thanks a lot dathu.qv! This solves the problem.

[#7]

Your welcome and It take a lot of time to get solution.

[#8]

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!