Match function dinamic no in script

[fkeuroglian]

Hi expert i have this script

Product:
LOAD Item,
Description,
Cost
FROM
Product.xlsx
(ooxml, embedded labels, table is Sheet1)
where match(Item,'1','4','5','7');

I am limitate to load only the item 1,4,5 or 7, ok fine, but this code is hardcode and i want to know if there is the possibility to do it dinamic

for example if then i want to add or remove some condition from mi match i go to the excel and delete or add , but only in the excel not in the script and the app will add this item to the match function or delete.

i attached this simple example, thanks a lot

Fernando K.

[sunny_talwar]

Not sure I understand how you plan to make all this dynamic? You want to use variable instead?

[settu_periasamy]

I think you can create one more table ( temprary for e.g. sheet2) with item code only..

Then you can use the where exists in your second table.

Then drop your first table..

Temp_item:

Load item from sheet2;

Product:

Load *  from sheet1 where exists (item);

Drop table Temp_item;

You can remove or add item data in your excel..

I believe this is what you are looking..

[sunny_talwar · Accepted solution]

May be something like this:

ProductList:

LOAD ItemList

FROM

Product.xlsx

(ooxml, embedded labels, table is Sheet2);

Product:

LOAD Item,

     Description,

     Cost

FROM

Product.xlsx

(ooxml, embedded labels, table is Sheet1)

where Exists(ItemList, Item);

Where you maintain a list of Items to be brought into the application in the Excel file.

UPDATE: Attached the Excel which include a new tab (Sheet 2) for list of items to be brought into the application.

[tamilarasu]

Another solution using match function,

Temp:
LOAD Concat(Chr(39) & ItemList & Chr(39),',') as ItemList
FROM

(ooxml, embedded labels, table is Sheet2);

Let vItemList = Peek('ItemList',0);

Drop Table Temp;

Product:
LOAD Item, 
Description, 
Cost
FROM

(ooxml, embedded labels, table is Sheet1)
where match(Item,$(vItemList));

[fkeuroglian]

Thanks Sunny, this work and is what i am looking for!

[sunny_talwar]

Awesome