How I can create a single button to select one or more fields. attached is the practice sheet. in the sheet there is description and months (Jan to Dec) are my field name. I want to create a button to select "Sep,Oct,Nov & Dec" and string value should be less than and equal to Zero.
Can I suggest that you expand your script to include a flag for values less or equal to Zero then in your UI the button that select the month should include another Select In Field FIELD = [Flag_SalesValue_LessOrEqualZero] AND VALUE = 1 . See below
// this is your original script loading from excel file tmp: LOAD Desctiption, Jan18, Feb18, Mar18, Apr18, May18, Jun18, Jul18, Aug18, Sep18, Oct18, Nov18, Dec18 FROM Data.qvd (qvd);
tmp1:
CrossTable(Month,Sales) load * resident tmp;
drop table tmp;
Finaltb: LOAD *, IF(Sales <= 0,1,0) AS [Flag_SalesValue_LessOrEqualZero] // check if sales or whatever fieldname this is less or equal to zero ; LOAD *, LEFT(Month,3) As [tmp_Month] RESIDENT tmp1;
The solution provided by Rob is right, what I think you need to add is transform your data. Using CROSSTABLE function. See below and attached script
// this is your original script loading from excel file tmp: LOAD Desctiption, Jan18, Feb18, Mar18, Apr18, May18, Jun18, Jul18, Aug18, Sep18, Oct18, Nov18, Dec18 FROM Data.qvd (qvd);
tmp1:
CrossTable(Month,Sales) load * resident tmp;
drop table tmp;
Finaltb: LOAD *, LEFT(Month,3) As [tmp_Month] RESIDENT tmp1;
drop Table tmp1;
In the UI, for the button to work use tmp_Month field in Select In FIeld then Search String = (Oct|Nov|Dec) .
The solution provided by Rob is right, what I think you need to add is transform your data. Using CROSSTABLE function. See below and attached script
// this is your original script loading from excel file tmp: LOAD Desctiption, Jan18, Feb18, Mar18, Apr18, May18, Jun18, Jul18, Aug18, Sep18, Oct18, Nov18, Dec18 FROM Data.qvd (qvd);
tmp1:
CrossTable(Month,Sales) load * resident tmp;
drop table tmp;
Finaltb: LOAD *, LEFT(Month,3) As [tmp_Month] RESIDENT tmp1;
drop Table tmp1;
In the UI, for the button to work use tmp_Month field in Select In FIeld then Search String = (Oct|Nov|Dec) .
The solution you have given is of selecting Months as string. my requirement is of value as string and Fields should be multiple months. please find the attachment for the sample.
Can I suggest that you expand your script to include a flag for values less or equal to Zero then in your UI the button that select the month should include another Select In Field FIELD = [Flag_SalesValue_LessOrEqualZero] AND VALUE = 1 . See below
// this is your original script loading from excel file tmp: LOAD Desctiption, Jan18, Feb18, Mar18, Apr18, May18, Jun18, Jul18, Aug18, Sep18, Oct18, Nov18, Dec18 FROM Data.qvd (qvd);
tmp1:
CrossTable(Month,Sales) load * resident tmp;
drop table tmp;
Finaltb: LOAD *, IF(Sales <= 0,1,0) AS [Flag_SalesValue_LessOrEqualZero] // check if sales or whatever fieldname this is less or equal to zero ; LOAD *, LEFT(Month,3) As [tmp_Month] RESIDENT tmp1;
