purge char after initiating crosstable

[124rooski]

I am currently pivotting my data in the script:

CrossTable(CY, Sales, 3)

LOAD State,
City,
Category,
CY2020,
CY2021,
CY2022,
CY2023,
CY2024,
CY2025,
CY2026;

This way my CalendarYear (CY) is in its own column. In the front end, I want the values for CY column to be just the year. So in my expression I did: =PurgeChar(CY,'CY'). My problem is when a CY is selected, the breadcrumb will show up with the purge char formula at the top. What is the best way to by pass this? I tried initiating a purge char in the load script but it is not working as expected.

[lironbaram · Accepted solution]

hi

just run this script , 

this is a case where you want to handle it in the script part 

dataTemp:
CrossTable(CY,Sales,3)
load * inline [
State,City,Category,CY2020,CY2021,CY2022,CY2023,CY2024,CY2025,CY2026
NY,NY,AA,1,2,3,4,5,6,7
];


data:
NoConcatenate load State,
City,
Category,
num(PurgeChar(CY,'CY')) as CY,
Sales
Resident dataTemp;

drop table dataTemp;

[124rooski]

Much appreciated, did a few tweaks and its working perfectly!