FOR NEXT problem Incremental Load - Qlik Community

Anonymous · ‎2011-02-17

I have an app where I want to look through a set of historical quarterly qvd's, replace the missing ones and replace the current quarter at each reload.

The code ALMOST works. I am using a FOR NEXT loop to go through the quarters and an IF statement to decide if it meets any of the requirements that necessitate production of a new QVD. It continues on to the next quarter when skipping, but after writing the first QVD, my peek statement does not find the next quarter... It comes up null. This of course results in an error once the IF statement processes the date like '/1/'. I posted a sample of my HIGHLY SIMPLIFIED CODE below to give an example, but the example happens to work fine. I have also posted the whole tab of code but it won't be runnable stand-alone.

Here is the pertinent log where the error occurs. Notice the PEEK satement populates with a row number, but the variable does not get populated. The table still exists so why is it not picking up a value?

2/17/2011 12:46:14 PM: 0574 LET vPeekRow = 7-1
2/17/2011 12:46:14 PM: 0575 LET vYearQuarterCursor = PEEK('YearQuarter',6,TempYearQuarter)
2/17/2011 12:46:14 PM: 0576 TRACE vYearQuarterCursor=
2/17/2011 12:46:14 PM: 0576 vYearQuarterCursor=

Any thoughts are appreciated.

Simplified example (this will work fine in QV stand-alone):

TempYearQuarter:
LOAD * INLINE [
 YearQuarterRowNum, YearQuarter
 1, 2009-Q1
 2, 2009-Q2
 3, 2009-Q3
 4, 2009-Q4
 5, 2010-Q1
 6, 2010-Q2
 7, 2010-Q3
 8, 2010-Q4
 9, 2011-Q1
 10, 2011-Q2
];


// Which YearQuarter is current date in?
LET vCurrentYearQuarter = YEAR(TODAY()) & '-' & 'Q' & CEIL(Month(TODAY())/3);

// Remainder of this script tab is a loop that creates QVD's for each year-quarter combination that exists for
// sales, aggsales and forecasts.

LET vYearQuarterRowNumMin = PEEK('YearQuarterRowNum',0,TempYearQuarter);
LET vYearQuarterRowNumMax = PEEK('YearQuarterRowNum',-1,TempYearQuarter);
LET vYearQuarterRowNumCursor = $(vYearQuarterRowNumMin);

FOR vYearQuarterRowNumCursor = $(vYearQuarterRowNumMin) TO $(vYearQuarterRowNumMax)

 LET vStartTime = NOW();


Let vQuery = len(tablenumber('TempYearQuarter'));
TRACE Length= $(vQuery);

 LET vPeekRow = $(vYearQuarterRowNumCursor)-1;
 LET vYearQuarterCursor = PEEK('YearQuarter',$(vPeekRow),TempYearQuarter);
 TRACE vYearQuarterCursor=$(vYearQuarterCursor);
 LET vDateMin = ((RIGHT('$(vYearQuarterCursor)',1)*3)-2) & '/1/' & LEFT('$(vYearQuarterCursor)',4);
 TRACE vDateMin=$(vDateMin);
 LET vDateMax = (RIGHT('$(vYearQuarterCursor)',1)*3) & IF(RIGHT('$(vYearQuarterCursor)',1)= 2 OR RIGHT('$(vYearQuarterCursor)',1)= 3 ,'/30/','/31/') & LEFT('$(vYearQuarterCursor)',4);
 TRACE vDateMax=$(vDateMax);

 // Set conditions for creating a QVD pair.
 IF ($(vYearQuarterCursor) = $(vCurrentYearQuarter)) THEN

 TRACE This would create QVD for $(vYearQuarterCursor);
 LET vEndTime = NOW();

 ELSE
 TRACE This would skip QVD for $(vYearQuarterCursor);
 END IF

NEXT

DROP TABLE TempYearQuarter;

Real Code (where is my problem?):

// Create a table with all the quarters sales and forecasts exist for.
TempYearQuarter1:
LOAD DISTINCT YEAR(Date) & '-' & 'Q' & CEIL(Month(Date)/3) AS YearQuarter;
//ROWNO() AS YearRowNum;
SQL SELECT *
FROM $(vDB).dbo."v_AggregatedSalesHistoryProfit";
//ORDER BY Date;

JOIN (TempYearQuarter1)
LOAD DISTINCT YEAR(ForeCastDate) & '-' & 'Q' & CEIL(Month(ForeCastDate)/3) AS YearQuarter;
//ROWNO() AS YearRowNum;
SQL SELECT *
FROM $(vDB).dbo.ForecastGroupScenario;

TempYearQuarter:
LOAD DISTINCT YearQuarter,
 ROWNO() AS YearQuarterRowNum
RESIDENT TempYearQuarter1
ORDER BY YearQuarter;
DROP TABLE TempYearQuarter1;

// Which YearQuarter is current date in?
LET vCurrentYearQuarter = YEAR(TODAY()) & '-' & 'Q' & CEIL(Month(TODAY())/3);

// Remainder of this script tab is a loop that creates QVD's for each year-quarter combination that exists for
// sales, aggsales and forecasts.

LET vYearQuarterRowNumMin = PEEK('YearQuarterRowNum',0,TempYearQuarter);
LET vYearQuarterRowNumMax = PEEK('YearQuarterRowNum',-1,TempYearQuarter);
LET vYearQuarterRowNumCursor = $(vYearQuarterRowNumMin);

FOR vYearQuarterRowNumCursor = $(vYearQuarterRowNumMin) TO $(vYearQuarterRowNumMax)

 LET vStartTime = NOW();

 LET vPeekRow = $(vYearQuarterRowNumCursor)-1;
 LET vYearQuarterCursor = PEEK('YearQuarter',$(vPeekRow),TempYearQuarter);
 TRACE vYearQuarterCursor=$(vYearQuarterCursor);
 LET vDateMin = ((RIGHT('$(vYearQuarterCursor)',1)*3)-2) & '/1/' & LEFT('$(vYearQuarterCursor)',4);
 TRACE vDateMin=$(vDateMin);
 LET vDateMax = (RIGHT('$(vYearQuarterCursor)',1)*3) & IF(RIGHT('$(vYearQuarterCursor)',1)= 2 OR RIGHT('$(vYearQuarterCursor)',1)= 3 ,'/30/','/31/') & LEFT('$(vYearQuarterCursor)',4);
 TRACE vDateMax=$(vDateMax);

 // Set conditions for creating a QVD pair.
 IF ($(vYearQuarterCursor) = $(vCurrentYearQuarter)) OR (ISNULL(QVDCREATETIME('QVD\$(vDB)\$(vVers)GrpAggSalesForecastComp_$(vYearQuarterCursor).qvd'))) OR (ISNULL(QVDCREATETIME('QVD\$(vDB)\$(vVers)SalesHistory_$(vYearQuarterCursor).qvd'))) THEN

 //Must load aggsales and forecast together as they will be compared against each other.
 //AggregatedSales Forecast JOINS
 //AggregateSalesHistory:
 AggSalesForecastCompTemp:
 LOAD //HASH128(PriceZoneSID & ProdOrAggSID & Date(Date)) AS %_ZoneEntityDate,
 (PriceZoneSID & ProdOrAggSID & Date(Date)) AS %_ZoneEntityDate,
 Date(Date) AS Date,
 PriceZoneSID,
 ProdOrAggSID,
 ProdOrAggSID AS EntitySID,
 Sales,
 Cost,
 Price,
 profit;
 SQL SELECT *
 FROM $(vDB).dbo."v_AggregatedSalesHistoryProfit"
 WHERE PriceZoneSID NOT IN ('05F65C7D-1234-5678-0002-000000000000','E14C6B32-F1E1-43AF-8813-A951D1BA9E53')
 //AND SegmentID = 0
 AND Date >= convert(datetime,'$(vDateMin)',0) AND Date <= convert(datetime,'$(vDateMax)',0);

 AggSalesForecastComp:
 LOAD %_ZoneEntityDate,
 Date,
 PriceZoneSID,
 ProdOrAggSID,
 EntitySID,
 SUM(Sales) AS AggSalesUnits,
 SUM(Cost * Sales) AS AggSalesCost,
 SUM(Price * Sales) AS AggSalesRevenue,
 SUM(profit) AS AggSalesProfit
 RESIDENT AggSalesForecastCompTemp
 GROUP BY %_ZoneEntityDate, Date, PriceZoneSID, ProdOrAggSID, EntitySID;
 DROP TABLE AggSalesForecastCompTemp;

 //ForcastProductScenario
 ForecastProductScenarioTemp:
 LOAD DATE(ForecastDate) AS Date,
 ProductSID AS ProdOrAggSID,
 ZoneSID AS PriceZoneSID,
 CashSales,
 UnitSales,
 Profit;
 SQL SELECT *
 FROM $(vDB).dbo.ForecastProductScenario
 WHERE ZoneSID NOT IN ('05F65C7D-1234-5678-0002-000000000000','E14C6B32-F1E1-43AF-8813-A951D1BA9E53')
 //AND SegmentID = 0
 AND ForecastDate >= convert(datetime,'$(vDateMin)',0) AND ForecastDate <= convert(datetime,'$(vDateMax)',0);

 JOIN (AggSalesForecastComp)
 LOAD Date,
 ProdOrAggSID,
 PriceZoneSID,
 SUM(CashSales) AS ForecastSalesRevenue,
 SUM(UnitSales) AS ForecastUnits,
 SUM(Profit) AS ForecastProfit
 RESIDENT ForecastProductScenarioTemp
 GROUP BY Date, ProdOrAggSID, PriceZoneSID;
 DROP TABLE ForecastProductScenarioTemp;

 //GroupAggregateSalesHistory JOINS
 //GrpAggSales
 GrpAggSalesForecastCompTemp:
 LOAD //HASH128(ZoneSID & EntitySID & Date(Date)) AS %_ZoneEntityDate,
 (ZoneSID & EntitySID & Date(Date)) AS %_ZoneEntityDate,
 Date(Date) AS Date,
 //ZoneSID AS PriceZoneSID,
 //EntitySID AS EntitySID,
 Price,
 Sales;
 SQL SELECT *
 FROM $(vDB).dbo.GroupAggregatedSalesHistory
 WHERE ZoneSID NOT IN ('05F65C7D-1234-5678-0002-000000000000','E14C6B32-F1E1-43AF-8813-A951D1BA9E53')
 //AND SegmentID = 0
 AND Date >= convert(datetime,'$(vDateMin)',0) AND Date <= convert(datetime,'$(vDateMax)',0);

 GrpAggSalesForecastComp:
 LOAD %_ZoneEntityDate,
 Date,
 //ZoneSID AS PriceZoneSID,
 //EntitySID AS EntitySID,
 SUM(Price * Sales) AS GrpAggSalesRevenue
 RESIDENT GrpAggSalesForecastCompTemp
 GROUP BY Date, %_ZoneEntityDate;
 DROP TABLE GrpAggSalesForecastCompTemp;

 //ForecastGroupScenario
 ForecastGroupScenarioTemp:
 LOAD //HASH128(ZoneSID & EntitySID & Date(ForeCastDate)) AS %_ZoneEntityDate,
 (ZoneSID & EntitySID & Date(ForeCastDate)) AS %_ZoneEntityDate,
 DATE(ForeCastDate) AS Date,
 ZoneSID AS PriceZoneSID,
 EntitySID,
 CashSales,
 UnitSales,
 Profit;
 SQL SELECT *
 FROM $(vDB).dbo.ForecastGroupScenario
 WHERE ZoneSID NOT IN ('05F65C7D-1234-5678-0002-000000000000','E14C6B32-F1E1-43AF-8813-A951D1BA9E53')
 //AND SegmentID = 0
 AND ForeCastDate >= convert(datetime,'$(vDateMin)',0) AND ForeCastDate <= convert(datetime,'$(vDateMax)',0);

 //ForecastGroupScenario
 JOIN (GrpAggSalesForecastComp)
 LOAD %_ZoneEntityDate,
 Date,
 PriceZoneSID,
 EntitySID,
 SUM(CashSales) AS ForecastGrpSalesRevenue,
 SUM(UnitSales) AS ForecastGrpUnits,
 SUM(Profit) AS ForecastGrpProfit
 RESIDENT ForecastGroupScenarioTemp
 GROUP BY %_ZoneEntityDate, EntitySID, PriceZoneSID, Date;
 DROP TABLE ForecastGroupScenarioTemp;

 STORE GrpAggSalesForecastComp INTO QVD\$(vDB)\$(vVers)GrpAggSalesForecastComp_$(vYearQuarterCursor).qvd;
 DROP TABLE GrpAggSalesForecastComp;

 // Load Sales History
 ProductTemp:
 LOAD ProductID,
 ProductSID;
 SQL SELECT *
 FROM $(vDB).dbo.Product;

 SalesHistoryTemp:
 LOAD Date(SaleDate) AS Date,
 OutletID,
 ProductID,
 Cost,
 Price,
 Sales;
 SQL SELECT *
 FROM $(vDB).dbo.SalesHistory
 WHERE SaleDate >= convert(datetime,'$(vDateMin)',0) AND SaleDate <= convert(datetime,'$(vDateMax)',0);

 SalesHistoryTemp2:
 LOAD Date,
 OutletID,
 ProductID,
 //Start_Update_10/15
 MAX(Price) AS PriceDetail,
 MAX(Cost) AS CostDetail,
 //End_Update_10/15
 SUM(Sales) AS SalesUnits,
 SUM(Cost * Sales) AS SalesCost,
 SUM(Price * Sales) AS SalesRevenue,
 SUM(Price * Sales) - SUM(Cost * Sales) AS SalesProfit
 RESIDENT SalesHistoryTemp
 GROUP BY Date, OutletID, ProductID;
 DROP TABLE SalesHistoryTemp;

 //Create list of zones and stores so we can add the zone into the sales history detail by outlet
 TempOutlet:
 LOAD PriceZoneSID,
 Name AS PriceZone;
 SQL SELECT *
 FROM $(vDB).dbo.PriceZone
 WHERE ClientPriceZoneID <> 'GLOBAL';

 LEFT JOIN (TempOutlet)
 LOAD PriceZoneSID,
 OutletSID;
 SQL SELECT *
 FROM $(vDB).dbo.PriceZoneOutlet;

 // Add stores (Outlets)
 LEFT JOIN (TempOutlet)
 LOAD
 OutletSID,
 OutletID;
 SQL SELECT *
 FROM $(vDB).dbo.Outlet;

 //Join the PriceZoneSID back into Sales History
 LEFT JOIN (SalesHistoryTemp2)
 LOAD OutletID,
 PriceZoneSID
 RESIDENT TempOutlet;
 DROP TABLE TempOutlet;

 LEFT JOIN (SalesHistoryTemp2)
 LOAD ProductID,
 ProductSID AS ProdOrAggSID,
 ProductSID AS EntitySID
 RESIDENT ProductTemp;
 DROP TABLE ProductTemp;

 //Start_Update_10/15
 // ForecastProductScenario joining to SalesHistory detail ONLY for detail price/cost counting
 LEFT JOIN (SalesHistoryTemp2)
 LOAD DATE(ForecastDate) AS Date,
 ProductSID AS ProdOrAggSID,
 ZoneSID AS PriceZoneSID,
 Price AS ForecastPrice,
 Cost AS ForecastCost,
 1 AS ForecastFlag;
 SQL SELECT *
 FROM $(vDB).dbo.ForecastProductScenario
 WHERE ZoneSID NOT IN ('05F65C7D-1234-5678-0002-000000000000','E14C6B32-F1E1-43AF-8813-A951D1BA9E53')
 //AND SegmentID = 0
 AND ForecastDate >= convert(datetime,'$(vDateMin)',0) AND ForecastDate <= convert(datetime,'$(vDateMax)',0);

 // Now compare Forecast Price to PriceDetail and cost fields to come up with matches and non-matches
 SalesHistory:
 LOAD Date,
 OutletID,
 ProductID,
 //PriceDetail,
 //CostDetail,
 SalesUnits,
 SalesCost,
 SalesRevenue,
 SalesProfit,
 ProdOrAggSID,
 PriceZoneSID,
 //ForecastPrice,
 //ForecastCost,
 //will compare against sum of forecast flag for non-weighted complaince
 IF(ForecastPrice=PriceDetail,1,0) AS SamePrice,
 IF(ForecastCost=CostDetail,1,0) AS SameCost,
 //will compare against sum of forecastunitsflag for weighted complaince
 IF(ForecastPrice=PriceDetail,SalesUnits,0) AS WeightedPrice,
 IF(ForecastCost=CostDetail,SalesUnits,0) AS WeightedCost,
 ForecastFlag,
 IF(ForecastFlag=1,SalesUnits) AS ForecastUnitsFlag,
 EntitySID
 RESIDENT SalesHistoryTemp2;
 DROP TABLE SalesHistoryTemp2;

 //End_Update_10/15

 //Load AggSalesAndForecast back in
 CONCATENATE (SalesHistory)
 LOAD %_ZoneEntityDate,
 Date,
 PriceZoneSID,
 ProdOrAggSID,
 EntitySID,
 AggSalesUnits,
 AggSalesCost,
 AggSalesRevenue,
 AggSalesProfit,
 ForecastSalesRevenue,
 ForecastUnits,
 ForecastProfit
 RESIDENT AggSalesForecastComp;
 DROP TABLE AggSalesForecastComp;


 //Load PriceChanges
 CONCATENATE (SalesHistory)
 LOAD PriceZoneSID,
 ProdOrAggSID,
 Date,
 PriceChange
 FROM QVD\$(vDB)\$(vVers)PriceChange.qvd
 (qvd)
 WHERE Date >= $(vDateMin) AND Date <= $(vDateMax);

 STORE SalesHistory INTO QVD\$(vDB)\$(vVers)SalesHistory_$(vYearQuarterCursor).qvd;

 LET vRecords = NoOfRows('SalesHistory');
 DROP TABLE SalesHistory;

 LET vEndTime = NOW();
 JOIN (Statistics)
 // Change the name of the table here:
 LOAD DISTINCT 'SalesHistory_'&'$(vYearQuarterCursor)' AS TableName,
 '$(vStartTime)' AS StartTime,
 '$(vEndTime)' AS EndTime,
 INTERVAL(NUM('$(vEndTime)') - NUM('$(vStartTime)'),'hh:mm:ss') AS Duration,
 $(vRecords) AS Records
 RESIDENT Statistics;

 ELSE
 END IF

NEXT

DROP TABLE TempYearQuarter;

Report Inappropriate Content · ‎2011-02-17

Before the end of the For next, try saving the QVD files in a temporary directory.

It´s work for me.


//LE TODOS CUBOS DA TABELA TEMPORARIA
TMP_CONFIGURACAO_CUBO:
LOAD DISTINCT
AL4_CODIGO
FROM TMP\TMP_CONFIGURACAO_CUBO.QVD (qvd);

//LACO PARA PEGAR OS DADOS DE CADA CUBO
FOR I=1 TO fieldValueCount('AL4_CODIGO')
LET CODIGO = TRIM(fieldvalue('AL4_CODIGO',$(I)));
TRACE Carregando agora $(CODIGO);
/BUSCA AS RESTRICOES DE CADA CUBO
TMP_DADOS_$(CODIGO):
LOAD DISTINCT
AL4_CODIGO,
AL4_FILTER,
AL4_EXPRIN,
AL4_EXPRFI
FROM TMP\TMP_CONFIGURACAO_CUBO.QVD (qvd)
WHERE AL4_CODIGO = '$(CODIGO)';

LET FILTRO = TRIM(fieldvalue('AL4_FILTER',1));
LET INICIO = TRIM(fieldvalue('AL4_EXPRIN',1));
LET FINAL = TRIM(fieldvalue('AL4_EXPRFI',1));

 //SE O CUBO POSSUI FILTRO USAR, CASO CONTRARIO USAR UR INICIAL E UR FINAL
 IF IsNull(TRIM(fieldvalue('AL4_FILTER',1))) OR LEN(TRIM(fieldvalue('AL4_FILTER',1))) < 5 THEN
 SET FILTRO = TRIM(AK5_CODIGO) >= '$(INICIO)' AND TRIM(AK5_CODIGO)<= '$(FINAL)';
END IF
//BUSCAR AS URS COM TAMANHO DE 12 CARACTERES POR CUBO
TMP_FILTRO_POR_UR_$(CODIGO):
LOAD
 CODIGO AS [Código cubo],
 "AK5_CODIGO" AS [Código unidade resultado];
 SQL SELECT AK5_CODIGO,
 '$(CODIGO)' AS CODIGO
 FROM SA_SIGA.AK5900
WHERE
($(FILTRO)) AND
LENGTH(TRIM(AK5_CODIGO)) = 12 AND
 AK5_MSBLQL = 2 AND
 D_E_L_E_T_ = ' '
ORDER BY "AK5_CODIGO";

 //GRAVA DADOS TEMPORARIOS
 STORE TMP_FILTRO_POR_UR_$(CODIGO) INTO TMP\TMP_FILTRO_POR_UR_$(CODIGO).QVD;

DROP TABLE TMP_DADOS_$(CODIGO);
DROP TABLE TMP_FILTRO_POR_UR_$(CODIGO);

LET CODIGO = Null();
LET FILTRO = Null();
LET INICIO = Null();
LET FINAL = Null();
NEXT

DROP TABLE TMP_CONFIGURACAO_CUBO;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//BUSCA TODOS CUBOS COM SUAS RESPECTIVAR URS
ACESSO_UR_CUBO:
LOAD * FROM TMP\TMP_FILTRO_POR_UR_*.QVD (qvd);

Anonymous · ‎2011-02-17

I commented everything between IF and ELSE and then uncommented a section at a time. I found the culprit. For some reason, it wanted me to completely drop any tables that would accessed again.

Thanks for assist.

rwunderlich · ‎2011-02-17

Aaron,

I think your problem was forgetting to quote the tablename in the peek() function. See
http://community.qlik.com/wikis/qlikview-wiki/forgetting-quotes-in-peek.aspx

for an explanation of why it worked the forst time and not subsequently (current table change). By dropping the new table, you made TempYearQuater the current table again. But I think you can fix it by just adding the quotes.