Subtract one for specified date

leobrand — Thu, 02 May 2019 02:43:50 GMT

If [EndDate] is equal to any of these dates (2/9/2019, 2/16/2019, 2/23/2019) then I want

[FinalDifference] = [PreDifference] – 1

EndDate	PreDifference	FinalDifference
2/1/2019	1	1
2/9/2019	1	0
2/16/2019	1	0
2/17/2019	1	1
2/23/2019	1	0

Unfortunately I am putting 2/23/2019 into [FinalDifference] instead, can you help?

[FindDate]:
LOAD Concat(Chr(39) & [SubtractDate] & Chr(39), ',') as DateTable
;
LOAD *
INLINE [
SubtractDate,
2/9/2019,
2/16/2019,
2/23/2019,
]
;

Let vDateTable = Peek('DateTable');
DROP DateTable [FindDate];

[Summary]:
EndDate,
PreDifference,
IF([EndDate] = $(vDateTable),[PreDifference]-1) AS [FinalDifference]
RESIDENT [PreSummary];

Re: Subtract one for specified date

PrashantSangle — Thu, 02 May 2019 03:34:31 GMT

try with mapping load. try below logic

map1:
mapping LOAD *
INLINE [
EndDate,Flag
2/9/2019,1
2/16/2019,1
2/23/2019,1
]
;

[Summary]:
Load *,
IF(Flag=1,[PreDifference]-1) AS [FinalDifference];
Load *,
Applymap('map1',EndDate,'0') as Flag
EndDate,
PreDifference
RESIDENT [PreSummary];

Regards,
Prashant Sangle

topic Re: Subtract one for specified date in App Development

Subtract one for specified date

Re: Subtract one for specified date