Set Analysis to Load Script

[Sameer9585]

Hi,

I have a problem where I was using an expression in the chart . I am using below script in load editor  as below

set vStartDate = Date('2006-01-05');
set vEndDate = Date('2016-08-20');
set AsOfDimension = ResignedMonth;
// set vFFFinal = "count({<AsOfResignedMonth>} total ID) - Count(ID)" ;

Sheet4:
LOAD
ID,
// [Date of Resignation] &'|'& [Date of Termination] as DateLinkID,
[Date of Resignation] as [Date of Resignation],
[Date of Termination] as [Date of Termination],
[Date of Resignation] as %Date
FROM [lib://Desktop/sample3.xlsx]
(ooxml, embedded labels, table is Sheet3);

MasterCalendar:
LOAD
Month(%Date) as ResignedMonth,
Year([%Date]) as ResignedYear,
Monthname(%Date) as ResignedYearMonth,
dayname([%Date]) as ResignedDate,
%Date
;

LOAD
num(recno()-1+$(vStartDate)) as %Date
AutoGenerate $(vEndDate)-$(vStartDate)
WHERE 1=1;

tmpAsOfCal:
Load
distinct
$(AsOfDimension),
autonumber($(AsOfDimension),'AsOf') as $(AsOfDimension)Serial
Resident MasterCalendar
order by $(AsOfDimension);

Join (tmpAsOfCal)
Load
$(AsOfDimension) as AsOf$(AsOfDimension)
Resident tmpAsOfCal ;

[AsOfCalendar]:
Load
$(AsOfDimension),
AsOf$(AsOfDimension),
autonumber(AsOf$(AsOfDimension),'AsOf') - $(AsOfDimension)Serial as $(AsOfDimension)Diff
// If(YearDiff=0,1,0) as IsSameYear
Resident tmpAsOfCal
Where AsOf$(AsOfDimension) >= $(AsOfDimension)
order by $(AsOfDimension)
;

Drop Table tmpAsOfCal;

and now I have used the AsOfResinedMonth in Dimension and in measure I used an expression as count({<AsOfResignedMonth>} total ID) - Count(ID) to find the active Id and now I want to write the same expression in the load editor will this be possible .

Thanking You,

[JordyWegman]

Hi Sameer,

It looks like you start with a problem, but you end with a question if it's possible at all. Have you tried to define the formula in a variable? Does it work? Because it should work.

Jordy

Climber

[Sameer9585]

I tried using variable it doesn't worked

[JordyWegman]

When you put the variable in a text box between single quotes. What does it return? I see that you commented the variable out a.t.m.

Jordy

Climber

[Sameer9585]

It shows the below error

[JordyWegman]

You can't use set analysis in your load script. I thought you were talking about setting a formula in a variable with set analysis. 

For this, you need to use the where statement behind your FROM [YourSource] (qvd);

For example:

Load
  *
From [YourSource] (qvd)
Where AsOfResignedMonth = 'XXXXX'
;

Jordy

Climber

[Sameer9585]

Thanks for your reply but the problem is with the expression i.e count({<AsOfResignedMonth>} total ID) - Count(ID) and I tried an alternative count(total ID) - Count(ID) this expression also works in the chart I want count(total ID) - Count(ID) expression in the load editor is that possible

[Sameer9585]

I cannot use this one because I dont have any particular AsOfResignedMonth value so not works