topic Autogenerate Loop inside loop in QlikView

Autogenerate Loop inside loop

seWork — Thu, 14 Jun 2018 06:18:16 GMT

Hi all,

I'm trying to run the below script

The purpose is to create dates records(Autogenerated date + 1 for g records) per SKU based on avg. dates per Category

what am I doing wrong?

InventoryTemp_Gen:

Load DISTINCT ROWNO() AS SKUid,SKU,Category Resident Item_Master_Data

Where LEFT(SKU,3)='FI-';

left join

Load * Resident Temp_Inv_Dates_3;

Left JOIN

Load Category,AVG.Inv_Date as Inv_Date From qvd/avgnvtable.QVD(qvd);

drop table Temp_Inv_Dates_3;

Store InventoryTemp_Gen into qvd/InventoryTemp_Gen.QVD;

let vSKUsInv = FieldValuecount(FieldName(2,'InventoryTemp_Gen'));

for i = 1 to vSKUsInv

let vSKUsInvGenName = peek('SKU',$(i),'InventoryTemp_Gen');

let vSKUsInvGenCatName = peek('Category',$(i),'InventoryTemp_Gen');

let vSKUsInvGen = peek('Avg.#Inv_Date',$(i),'InventoryTemp_Gen');

let vSKUsInv_Date = peek('Inv_Date',$(i),'InventoryTemp_Gen');

If vSKUsInv<0 THEN

for g = 1 to vSKUsInvGen

let vSKUsInv_DateGen = date(num(vSKUsInv_Date)+g);

InventoryTemp:

Load '$(vSKUsInvGenName)' as SKU,

'$(vSKUsInvGenCatName)' as Category,

'$(vSKUsInv_DateGen)' as Inv_Date

AutoGenerate 1;

next g

next i

store InventoryTemp into qvd/InventoryTemp_Gen2.QVD;

Thank you

Re: Autogenerate Loop inside loop

jonathandienst — Thu, 14 Jun 2018 06:27:37 GMT

FieldValueCount applies to a field. I think you are attempting to use it for a table.

let vSKUsInv = FieldValuecount(FieldName(2,'InventoryTemp_Gen'));

This should probably be NoOfRows('InventoryTemp_Gen'). Or use QvdNoOfRecords('...QVD file name...').

Re: Autogenerate Loop inside loop

seWork — Thu, 14 Jun 2018 07:06:27 GMT

The vSKUsInv var is ok for both of the options and I'm getting the right value for it

The problem is that during the run with both of the options I'm receiving an error regarding the loop vars'

Semantic error

The control statement is not correctly matched with its corresponding start statement next i

and the result is only 1 row with 1 SKU instead of "g" rows per SKU("i" SKUS)

Re: Autogenerate Loop inside loop

seWork — Thu, 14 Jun 2018 10:27:04 GMT

My full script is

I'm getting the same error for various of loops

Semantic error

The control statement is not correctly matched with its corresponding start statement next %"

Inventory:

LOAD

Distinct $(vInventory_Calc)

FROM

Inventory.qvd

(qvd)

Where Inv_Pallets>0

and not isnull(Inv_Pallets);

RENAME Field Inv_Date to old.Inv_Date;

Left join

Load Distinct(old.Inv_Date) as old.Inv_Date,date(old.Inv_Date) as Inv_Date Resident Inventory;

drop Field old.Inv_Date;

Left join

Load Distinct SKU,Category Resident Item_Master_Data;

store Inventory into qvd/Inventory.QVD;

let vTablei = NoOfFields('Inventory');

avgnvtable :

load distinct Category resident Inventory;

FOR ia = 1 to vTablei

IF ia<vTablei THEN

let fname = FieldName(ia,'Inventory');

inner join (avgnvtable)

Load Category

,if(isnum(maxString($(fname))),CEIL(Avg($(fname))),subfield(concat($(fname),'/'),'/',ceil(rand()*count($(fname))))) as AVG.$(fname)

Resident Inventory group by Category;

IF fname = 'Category' THEN

drop Field AVG.Category;

ELSEIF fname ='SKU' THEN

drop Field AVG.SKU;

ELSE

ENDIF

Next ia;

ELSE

EXIT FOR

ENDIF

RENAME Field AVG.Inv_Date TO old.AVG.Inv_Date;

Left Join(avgnvtable)

Load distinct(old.AVG.Inv_Date) as old.AVG.Inv_Date,Date(old.AVG.Inv_Date) as AVG.Inv_Date Resident avgnvtable;

Drop Field old.AVG.Inv_Date From avgnvtable;

Store avgnvtable into qvd/avgnvtable.QVD;

Drop Table avgnvtable;

ELSE

end if

Temp_Inv_Dates_1:

Load Distinct SKU,Category,COUNT(Inv_Date) as #Inv_Date Resident Inventory

Group by SKU,Category;

Store Temp_Inv_Dates_1 into qvd/Temp_Inv_Dates_1.QVD;

Temp_Inv_Dates_2:

Load Category,Count(SKU) as #SKU,SUM(#Inv_Date) as Total#Inv_Date Resident Temp_Inv_Dates_1

Group by Category;

DROP TABLE Temp_Inv_Dates_1;

Store Temp_Inv_Dates_2 into qvd/Temp_Inv_Dates_2.QVD;

Temp_Inv_Dates_3:

Load Category,CEIL(Total#Inv_Date/#SKU) as Avg.#Inv_Date Resident Temp_Inv_Dates_2;

DROP TABLE Temp_Inv_Dates_2;

Store Temp_Inv_Dates_3 into qvd/Temp_Inv_Dates_3.QVD;

InventoryTemp_Gen:

Load DISTINCT ROWNO() AS SKUid,SKU,Category Resident Item_Master_Data

Where LEFT(SKU,3)='FI-';

let vSKUsInv = FieldValuecount(FieldName(2,'InventoryTemp_Gen'))-1;

if vSKUsInv>=1 then

left join(InventoryTemp_Gen)

Load * Resident Temp_Inv_Dates_3;

Left JOIN

Load Category,AVG.Inv_Date as Inv_Date From qvd/avgnvtable.QVD(qvd);

drop table Temp_Inv_Dates_3;

Store InventoryTemp_Gen into qvd/InventoryTemp_Gen.QVD;

for d = 1 to vSKUsInv

let vSKUsInvGenName = peek('SKU',$(d),'InventoryTemp_Gen');

let vSKUsInvGenCatName = peek('Category',$(d),'InventoryTemp_Gen');

let vSKUsInvGen = peek('Avg.#Inv_Date',$(d),'InventoryTemp_Gen');

let vSKUsInv_Date = peek('Inv_Date',$(d),'InventoryTemp_Gen');

for g = 1 to vSKUsInvGen

let vSKUsInv_DateGen = date(num(vSKUsInv_Date)+g);

InventoryTemp:

Load '$(vSKUsInvGenName)' as SKU,

'$(vSKUsInvGenCatName)' as Category,

'$(vSKUsInv_DateGen)' as Inv_Date

AutoGenerate 1;

next g;

next d;

store InventoryTemp into qvd/InventoryTemp_Gen2.QVD;

Left join

Load * From qvd/avgnvtable.QVD(qvd);

Drop Field AVG.Inv_Date from InventoryTemp;

let vTable = NoOfFields('InventoryTemp');

For i = 1 to vTable

let fname = FieldName(i,'InventoryTemp');

let Nfname = mid(fname,5,len(fname)-4);

If left(fname,4) = 'AVG.' THEN

RENAME Field $(fname) TO $(Nfname);

next i;

else

endif

store InventoryTemp into qvd/InventoryTemp.QVD;

drop table InventoryTemp;

drop table InventoryTemp_Gen;

else

endif

Inventory:

LOAD * ,SKU&'-'&Batch AS Rec_Inv_Key

FROM

qvd\Inventory.QVD

(qvd);

Concatenate

LOAD *,SKU&'-'&Batch AS Rec_Inv_Key

FROM qvd/InventoryTemp.QVD

(qvd);

drop Field Category from Inventory;

Inner JOIN(Inventory)

Load Distinct SKU,

Sum(Inv_Pallets) as Total_INV,

Count(DISTINCT Inv_Date) as N_Inv_Date,

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_AA),dual('AA',1),

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_A),dual('A',2),

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_B),dual('B',3),

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_C),dual('C',4),

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_D),dual('D',5),

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_E),dual('E',6),

if(Sum(Inv_Pallets)/Count(DISTINCT Inv_Date)>=$(Inv_ABCD_F),dual('F',7),dual('G',8)

))))))) AS Inv_ABCD

Resident Inventory

GROUP BY SKU;