Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

aaroncouran
Contributor III

FOR NEXT problem Incremental Load

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;


3 Replies
Not applicable

FOR NEXT problem Incremental Load

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


aaroncouran
Contributor III

FOR NEXT problem Incremental Load

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.

MVP & Luminary
MVP & Luminary

FOR NEXT problem Incremental Load

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.