variables are incorrectly assigned . Working fine for net cost and issue with group cost

[hrithikroshan]

Hello ,

I do have below 3 qvd's. From qvd names i am getting only date and trying to sort them in descending order. But i am unable to sort correctly for Group cost qvd's . However i am able to sort correctly for Net cost qvd's. I attached the document with the logic i am using and issue screen shot.

Kindly suggest.

examples of mine Group cost qvd names: 

1)Group Cost WWWW- 02-Sep-2019.qvd

2)Group Cost WWWW- 09-Sep-2019.qvd

3)Group Cost WWWW- 26-Aug-2019.qvd

examples of Net cost qvd names:

1)Net Cost WWWW- 02-Sep-2019.qvd

2)Net Cost WWWW- 09-Sep-2019.qvd

3)Net Cost WWWW- 26-Aug-2019.qvd

[Stoyan_Terziev]

Hi buddy,

I suspect the hardcoded length of 76 chars is the problem with your logic.

Here's how I'd propose you change the code:

SET FileName = '1)Net Cost WWWW- 02-Sep-2019.qvd';

FILES:
LOAD
Date#(Left(Right('$(FileName)', 15), 11),'DD-MMM-YYYY') AS SNAPSHOT_v1,
Date#(Mid('$(FileName)', Len('$(FileName)')-14, 11),'DD-MMM-YYYY') AS SNAPSHOT_v2
AutoGenerate 1
;

[hrithikroshan]

thanks for your reply

at the begining me too suspected the 76 chars but the code is running fine for net cost and not for group cost.
so we can ignore the 76 chars as the code is working fine for net cost.

the variables are not assigned correctly for group cost . i want to understand this.
the variables are assigned correectly for net cost.

you could see there are two for statements one is commented . i ran one after the other by commenting and got two different results.
i am expecting same variable assignment when i ran the code for group cost or net cost.