topic Using for each ... next in QlikView

Using for each ... next

Wed, 05 Aug 2009 18:59:31 GMT

I'm trying to run a report out of our main data system but becasue it's such a slow / complicated report in the back group I need to run the query for each site code (contract) in turn rather than just using a wild card.

I think the for each ... next function will do this but I'm just not quite clear on how to actually do it! Below is the report as it stands with a single base code in. Ideally I'd like to load the list of base codes I want to search for from an excel document.

Thanks, Emma

SQL select

contract,

purch_order_no,

purch_line_no,

purch_rel_no,

part_no,

serial_no,

lot_batch_no,

qty_received,

qty_shipped,

unit_meas,

received_date,

shipped_date,

cust_order_no,

cust_line_no,

cust_rel_no,

cust_line_item_no,

from_site,

state,

to_char(ifsapp.Inventory_Part_Config_API.Get_Inventory_Value_By_Method(contract, part_no, '*'), 'B99999990.99') unit_cost,

IFSAPP.PART_CATALOG_API.Get_Description(part_no) description

from

ifsapp.inventory_parts_in_transit

where

contract = '2SABD'

and

part_no between '0' and 'ZZZZZZZZZZZZZ';

Using for each ... next

hector — Wed, 05 Aug 2009 21:07:57 GMT

Hi, maybe you can use the for each in this way

For each contract in '2SABD','2SABC','3SABD'

DATA:
SQL select
 ...
 contract,
 purch_order_no,
 ...
 to_char(ifsapp.Inventory_Part_Config_API.Get_Inventory_Value_By_Method(contract, part_no, '*'), 'B99999990.99') unit_cost,
 IFSAPP.PART_CATALOG_API.Get_Description(part_no) description
from
 ifsapp.inventory_parts_in_transit
where
 contract = '$(contract)' and
 part_no between '0' and 'ZZZZZZZZZZZZZ'
;
next

I hope this can help u.

Regards.

Using for each ... next

shumailh — Mon, 23 Nov 2009 16:59:24 GMT

Hi all,

I have a small problem would appreciate if any one can help. I have developed an application which generates a qvd on daily basis now i want to update the process to merge qvd's in to 1 on daily basis. I tried to use for each statement and use concatenation but the concatenation not working fine... it create lots of dublications... may be i am not working right with the table alias "Active_Card" while loading. need help!!!



$(include=Configuration.txt);

let path= '$(prepath)' & '\Output\' & '$(mn)' & '\Cards\QVDs\';

let a = 1;

for each File in filelist ('$(path)ActiveCard_*.qvd')

let CurrentFileName = right('$(File)', (len('$(File)') - len('$(path)')) ) ;
let AC = 'ActiveCard_' & right('00' &'$(CurrentDay)', 2) & '.qvd';

 if a = 1 then
 Active_Card:
 LOAD *
 FROM [$(path)$(CurrentFileName)] (qvd);

 else

 Active_Card:
 LOAD
 limit,
 bal,
 delq,
 AIF
 RESIDENT Active_Card;
 CONCATENATE
 LOAD
 limit,
 bal,
 delq,
 AIF
 FROM [$(path)$(CurrentFileName)] (qvd);

 END IF

 a = a+1

next File ;

Regards,
Shumail Hussain

Using for each ... next

Mon, 23 Nov 2009 17:11:04 GMT

Hi,

To remove the duplications have you tried using something like NOT EXISTS(FieldName) in the load statement? The field name you use should be unique of course.

Hope this helps,

Emma

Using for each ... next

shumailh — Mon, 23 Nov 2009 18:39:00 GMT

Hi Emma,

Thanks for the reply... actually dublication here is some abnormal number of records... but i already resolve this issue.

Basically i was looking out the records by loading it in debug mode where i put a limit of 10 records and uncertainly i am getting higher / abnormal number of records with respect to higher date file... attached snapshot is showing the date field i have in each qvd.

when i use the full reload regardless of in debug mode... i got the valid number of records. I still don't know why this debug mode showing me this abnormal count..