topic Re: Match function dinamic no in script in QlikView

Match function dinamic no in script

fkeuroglian — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Match function dinamic no in script

sunny_talwar — Tue, 19 Apr 2016 00:03:13 GMT

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

Re: Match function dinamic no in script

settu_periasamy — Tue, 19 Apr 2016 00:09:04 GMT

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..

Re: Match function dinamic no in script

sunny_talwar — Tue, 19 Apr 2016 00:09:07 GMT

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.

Re: Match function dinamic no in script

tamilarasu — Tue, 19 Apr 2016 04:07:36 GMT

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));

Re: Match function dinamic no in script

fkeuroglian — Tue, 19 Apr 2016 12:00:16 GMT

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

Re: Match function dinamic no in script

sunny_talwar — Tue, 19 Apr 2016 12:15:32 GMT

Awesome