Correct my Script

[Sameer9585]

Hi,

I have a problem that the data contains the fields ID,Year,1,2,3,4,5..,16 and my scenario is that I need to combine 12,13,14,15,16 column to one one column 12 and rename those 12 columns to Jan,Feb,Mar..Dec. And I need a new field in the front end of YTD sales too.

I have tried one please correct me if there is any error? If you want see the data set look into the file below in it Sheet 5 is the data set

data:
LOAD
ID,
"Year",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
RangeSum("12","13","14","15","16") as "12_new"
FROM [lib://Desktop/sample.xlsx]
(ooxml, embedded labels, table is Sheet5);

Drop Fields "12","13","14","15","16";
Rename Field "12_new" to "12";

data2:
CrossTable("Month",Sales,2)
Load
*
Resident data;

map:
Mapping
LOAD * Inline [
Month,MonthN
1,Jan
2,Feb
3,Mar
4,Apr
5,May
6,Jun
7,Jul
8,Aug
9,Sep
10,Oct
11,Nov
12,Dec
](delimiter is ',');

data3:
Load
ApplyMap('map',"Month") as "MonthN",
*
Resident data2;
Drop Tables data,data2;

Thanks in advance,

[miskinmaz]

Rather you can go with below script

LOAD
ID,
"Year",
sum("1") as 1,
sum("2") as 2,
sum("3") as 3, etc.....
sum("11") as 11,
sum(12)+sum(13))+sum(14))+sum(15))+sum(16) as "12"
FROM [lib://Desktop/sample.xlsx]
(ooxml, embedded labels, table is Sheet5)
group by ID,
"Year";

[miskinmaz]

if this is not required then wht problem you are facing

[Benbassou]

Hello,

Why you using Rangsum and Sum

This can be made more easily

Something like :

Data_temp:
CrossTable(MonthNum, Données, 2)
LOAD ID,
Year,
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
"12"+"13"+"14"+"15"+"16" as "12"
FROM [lib://Desktop/sample.xlsx]
(ooxml, embedded labels, table is Sheet5);

NoConcatenate
Data:
Load
*,
Pick(Num#(MonthNum), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') as Month_label
Resident Data_temp;

Drop table Data_temp;

[Sameer9585]

How to calculate YTD?

[Benbassou]

With expression :

Sum({$<Year={$(=max(Year))}>}( [Data] ))

and if you want to ignore months selection use this : 

Sum({$<Year={$(=max(Year))},Month_label=>}( [Data] ))

Advice : try to use in the future a master calendar to manage dates, it's very helpful

https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

[Sameer9585]

Sorry YTD means should contain all the years and if I filter with month it should sum of sales till that month for each year and ID