Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

creating new fields in script

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:

 

   

MonthIndex NameIndex ValueJan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14
1/1/2014Index11101.00
2/1/2014Index11050.951
3/1/2014Index11201.091.1428571
4/1/2014Index11151.051.0952380.9583331
5/1/2014Index11301.181.2380951.0833331.1304351
1/1/2014Index22001.00
2/1/2014Index21200.601
3/1/2014Index21100.550.9166671
4/1/2014Index21000.500.8333330.9090911
5/1/2014Index21130.570.9416671.0272731.131
6/1/2014Index21140.570.951.0363641.141.008851
7/1/2014Index21100.550.91666711.10.9734510.9649121
8/1/2014Index21200.6011.0909091.21.0619471.0526321.0909091

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

7 Replies
sunny_talwar

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

sunny_talwar

Capture.PNG

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:

Capture.PNG

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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!