Incremental QVD

[Gerhard]

Hi, Hoping someone have done this before and can help me. I want to store campaigns in a QVD, each campaign has different criterion so I load the criterion for each campaign from an Excel file and then store the values in a variables. Then I load the variable in a where clause to return the required data set, at this point I need to keep this in memory and then repeat the process for the next campaign and in the end store it in one QVD which can then be used for analytics. 

I tried a for loop, so it loop through each campaign which works. But I am struggling to increment this data into a QVD. I can't store the QVD in the for loop because it overrides the previous campaign data. How do I either store the QVD for each campaign and then pick it up again to increment or concatenate all the tables into one table and then store in a QVD? 

tblCampaignsTemp:

LOAD

    "Campaign Number" as Cnumber,

    "Campaign Name" as Cname,

    Item as Citem

FROM [lib://DataFiles/Test Campaigns.xlsx]

(ooxml, embedded labels, table is Campaigns) where len("Campaign Name")>1;

//Populate variable for each field - this is then used in the sales where clause

FOR vRow = 0 TO $(vRowCount)-1

    LET vCampaignNumber = Peek('Cnumber', $(vRow), 'tblCampaignsTemp');

    LET vCampaignName = Peek('Cname', $(vRow), 'tblCampaignsTemp');    

    LET vShortItemNumber = Peek('Citem', $(vRow), 'tblCampaignsTemp');

NoConcatenate

//Create the sales for each campaign

PreCampaignSales:

load '$(vCampaignName)' as "Campaign Name",

  Item,

      Sales

FROM [lib://DataFiles/Test Campaigns.xlsx]

(ooxml, embedded labels, table is Sales) where wildmatch("Item",$(vShortItemNumber));    

next;

drop table tblCampaignsTemp;

[fldc2500 · Accepted solution]

You should be able to achieve both methods you are suggesting by implementing something like the following:

1. Multiple QVD's

By using one of your variables in the store path you can save each iteration in the loop in a seperate QVD.

//Populate variable for each field - this is then used in the sales where clause

FOR vRow = 0 TO $(vRowCount)-1

    LET vCampaignNumber = Peek('Cnumber', $(vRow), 'tblCampaignsTemp');

    LET vCampaignName = Peek('Cname', $(vRow), 'tblCampaignsTemp');    

    LET vShortItemNumber = Peek('Citem', $(vRow), 'tblCampaignsTemp');

//Create the sales for each campaign
NoConcatenate
PreCampaignSales:
load 
'$(vCampaignName)' as "Campaign Name",
Item,
Sales
FROM [lib://DataFiles/Test Campaigns.xlsx]
(ooxml, embedded labels, table is Sales) where wildmatch("Item",$(vShortItemNumber));

STORE PreCampaignSales INTO [lib://DataFiles/Campaign_$(vShortItemNumber).qvd] (qvd);

next
drop table tblCampaignsTemp;

2. One QVD 

In this scenario you first create a dummy table where you will concatenate all your iterations from the loop.

//Create a dummy table
TableName:
LOAD
null()       AS "Campaign Name"
AutoGenerate 0;

FOR vRow = 0 TO $(vRowCount)-1

    LET vCampaignNumber = Peek('Cnumber', $(vRow), 'tblCampaignsTemp');

    LET vCampaignName = Peek('Cname', $(vRow), 'tblCampaignsTemp');    

    LET vShortItemNumber = Peek('Citem', $(vRow), 'tblCampaignsTemp');



//Create the sales for each campaign
Concatenate(TableName)
load 
'$(vCampaignName)' as "Campaign Name",
Item,
Sales
FROM [lib://DataFiles/Test Campaigns.xlsx]
(ooxml, embedded labels, table is Sales) where wildmatch("Item",$(vShortItemNumber));


next

drop table tblCampaignsTemp;
STORE TableName INTO [lib://DataFiles/Campaigns.qvd] (qvd);

 Would something like this help?

[Gerhard]

That's brilliant thanks, I was going to use the multiple QVD's as a last resort but then you're stuck with all the QVD's. Your dummy table did the trick, I actually tried this but I did it in the loop rather than outside. Thanks so much for your help. Greatly appreciated.