Is it possible to load the attached excel as- is ?

[ramyasaiqv]

Hello Community Experts,

I'm not sure if it's possible to load the attached excel as-is, without breaking down the data into separate excels for each year. Currently it has only 2 yrs data but in the future data will be added and it should be dynamically updated. Please can some one help me or shed some light on this. Thank you,

Regards,

Ramya

[its_anandrjs]

Hi,

Load this way and find the attached also

tmp1:
CrossTable(MainCategory, Data, 5)
LOAD No,
Category,
Iten,
Owner,
[Item type],
[2016 Reference Line],
[42370],
[42401],
[42430],
[42461],
[42491],
[42522],
[42552],
[42583],
[42614],
[42644],
[42675],
[42705],
[2017 Reference Line],
[42736],
[42767],
[42795],
[42826],
[42856],
[42887],
[42917],
[42948],
[42979],
[43009],
[43040],
[43070]
FROM
C:\Users\Home\Desktop\Qcomm\Issues.xlsx
(ooxml, embedded labels, table is Cases);

DROP Field No;

NoConcatenate
Final:
LOAD
//No,
    Category,
Iten,
Owner,
[Item type],
Date( num#(MainCategory),'MMM-YY' ) as MainCategory,
Data
Resident tmp1;

DROP Table tmp1;

Regards

Anand

[ramyasaiqv]

Hi Anand,

Thank you very much for the response. I have 2 questions here

1. is it possible to show the 2016 Reference Line, 2017 Reference Line as one of the data value of the MainCategory? I cannot see the reference Line data,  they  are separate values for each year and are not part of the monthly data values

2. Currently the excel has 2016 and 2017 data but after 6 months if 2018 data is added to the excel i want that data to be updated automatically in the qvw, without manually loading it. Is that possible? Thank you so much your time and efforts

Ramya

[its_anandrjs]

Thanks

1. Off course i make changes on the script again and check this now but is your MonthName field why you show Reference Line data over there. But if requires you can use that one as well on the column see my script. For this i make changes on the script

if(Right(MonthName,4) = 'Line', MonthName,Date( num#(MonthName),'MMM-YY' )) as MonthNameFinal

2. Off Course you can add complete year on this excel and it is dynamically read your data from the excel file for this i make some changes again on the script have a look. To load all values use Load * From Location;

CrossTable(MonthName, Data, 5)
LOAD *
FROM
C:\Users\Home\Desktop\Qcomm\Issues.xlsx
(ooxml, embedded labels, table is Cases);

Complete script is here:-

tmp1:
CrossTable(MonthName, Data, 5)
LOAD *
FROM
C:\Users\Home\Desktop\Qcomm\Issues.xlsx
(ooxml, embedded labels, table is Cases);

DROP Field No;

NoConcatenate
Final:
LOAD
Category,
Iten,
Owner,
[Item type],
MonthName,
if(Right(MonthName,4) = 'Line', MonthName,Date( num#(MonthName),'MMM-YY' )) as MonthNameFinal,
Data
Resident tmp1;

DROP Table tmp1;

See attached file as well

[ramyasaiqv]

Thank you so much Anand, i really appreciate it.

I'm really sorry as you mentioned the Reference Line data should not be part of "MonthName" field, instead they should be separate field values  (Reference Line 2016, Reference 2017 etc) like Category, owner, Iten  is that possible? I'm sorry about the confusion.

Regards,

Ramya

[its_anandrjs]

Ok i got this and it is now available in your script as well i make changes for this field kindly check the script once again.

Regards

Anand

[ramyasaiqv]

Hi Anand,

Thank you so much for the response, it looks like you forgot to attach the updated qvw, please can you upload the updated qvw. Thank you,

Regards,

Ramya

[its_anandrjs · Accepted solution]

Find the attached

Regards,

Anand

[ramyasaiqv]

Anand,

Thank you so much and i don't want to pester you. Initially i was confused and said the Reference Line should be part of "MonthName" but that was my mistake. Actually the Reference Line 2016 and Reference 2017 should be separate fields, i want the Final table look like below script. Is that possible?I'm really sorry about the confusion and thank you so much for your help.

Final:
LOAD
Category,
Iten,
Owner,
[Item type],
MonthName,

[Reference Line 2016],

[Reference Line 2017],
//if(Right(MonthName,4) = 'Line', MonthName,Date( num#(MonthName),'MMM-YY' )) asMonthNameFinal,
Data