Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a excel file which i am trying to load into QV and attached is the sample, if you can look at the file in column D row 10 (Cell D10) we have Apr Wk1, Now my requirement is i need to just get that Apr value out of the file and display it as its showing the month.
Can someone throw any suggestions on this?
Thanks.
Hi Andre,
Sorry i unintentionally deleted your post.But that was the one i was looking for, Thanks a lot for your help.
LOAD left(BR, 3) as CellValue
FROM
where A = 'Reg';
HI
Try like this
CrossTable(Month, Value, 3)
LOAD Reg,
Dis,
[Dis Name],
[Apr Wk1] AS Apr
FROM
[Test (1).xlsx]
(ooxml, embedded labels, header is 8 lines, table is Sheet1);
Looks like we have hardcoded the Cell D10 value to be AprWk1 which cannot be done as this file keeps changing for every month and the month name changes in that cell.
Hi
Try like this
CrossTable(Month, Value, 3)
LOAD Reg,
Dis,
[Dis Name],
[Apr Wk1]
FROM
[Test (1).xlsx]
(ooxml, embedded labels, header is 8 lines, table is Sheet1);
NoConcatenate
Load Replace(Month,'Wk1','') AS Month, Reg,
Dis,
[Dis Name],
Value Resident Test;
DROP Table Test;
Hi,
Please correct me if i am wrong but looks like in the cross table again we are using the Apr Wk1 , we dont need this because the month name keeps changing, also i tried to run the above code and i receive an error, can you please make the change and send me a sample QVD which can work.
Thanks
HI
Can you provide your sample data which explain your scenario?
I've a solution but it may not be best practice.
You can use:
LOAD *
FROM
T123085Test.xlsx
(ooxml, embedded labels, header is 8 lines, table is Sheet1);
Then in your chart you need to create expressions conditional basis as the Field name is changing every month. Based on the current month the field name will be present in your data file and will be displayed in the Chart. but if the Month mismatches then it will not show any data.
Hi,
I dont have any sample data or anything and i dont even want any data , all i am looking for is a text box which shows the name of the month which is displayed in cell D10.
Please let me know if i am unclear.
Thanks
Hi
Hi,
Looks like we are trying to bring the data over here, but i dont want any data at all all i am looking for is a text box which shows the name of the month which is displayed in cell D10.
Please let me know if i am unclear.
Thanks
You'll have to bring some data over to display as you can't set a pointer from QV to an Excel Cell reference.