In below attached Load script, Update records is not Working. Please help to fix this.
$(Must_Include=$(vQVDRoot)\Sheets.txt);
If Alt(FileSize('$(vQVDRoot)'&'\*Contacts_*.qvd'), 0) <= 0 then set vLoad=2; //set 2 for no base file found ElseIf Alt(FileSize('$(vQVDRoot)'&'\*DatabaseSummary_*.qvd'), 0) <= 0 then set vLoad=1; //set 1 for first load ElseIf Alt(FileSize('$(vQVDRoot)'&'\*Contacts_*.qvd'), 0) > 0 Then If Alt(FileSize('$(vQVDRoot)'&'\*DatabaseSummary_*.qvd'), 0) > 0 then set vLoad=0; //set 0 for load & reload Endif End If
if(vLoad=1) then //Previous Months Database summary loading DatabaseSummary: LOAD ltrim(rtrim([dbMonth]&'-'&[dbYear]&'-'&[dbDetails])) as PrimaryKey, [dbMonth]&'-'&[dbYear] as [dbMonth], [dbDetails], [dbCount], Date(Now()) as [LoadDate] From [$(vQVDRoot)\Archive_2018.xlsx] (ooxml, embedded labels, table is [$(vaSheetName1)]);
//Storing Total DB Summary data in a seperate table for furture dashboard reference STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary.qvd] (qvd);
STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary_$(vMonth).qvd] (qvd); Elseif(vLoad=0) then //Current Month Database summary loading Contacts: LOAD * From [$(vQVDRoot)\Contacts_$(vMonth).qvd] (qvd);
//Current Month Database summary loading //From [$(vQVDRoot)\Contacts_$(vMonth).qvd] (qvd) DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'GA' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [GA] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'AMERICAS' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [AMS] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'EMEA' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [EMEA] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'APAC' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [APAC] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'ALL OTHERS' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where Not [GA] Like '*Yes*' And Not [AMS] Like '*Yes*' And Not [EMEA] Like '*Yes*' And Not [APAC] Like '*Yes*' Group by [ContactMonth];
DatabaseSummary: Load [dbMonth]&'-'&[dbDetails] as PrimaryKey, [dbMonth], [dbDetails], [dbCount],Date(Now()) as [LoadDate] Resident DatabaseSummaryTemp;
Concatenate(DatabaseSummary) LOAD ltrim(rtrim([dbMonth]&'-'&[dbDetails])) as Primarykey, [dbMonth], [dbDetails], [dbCount], [LoadDate] FROM [$(vQVDRoot)\DatabaseSummary.qvd] (qvd);
STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary.qvd] (qvd);
STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary_$(vMonth).qvd] (qvd);
Elseif(vLoad=2) then Trace Contact Base File Does not Exist!; END IF
1. Loading history summary data into a QVD files from a excel file until Oct-2018.
DatabaseSummary: LOAD ltrim(rtrim([dbMonth]&'-'&[dbYear]&'-'&[dbDetails])) as PrimaryKey, [dbMonth]&'-'&[dbYear] as [dbMonth], [dbDetails], [dbCount], Date(Now()) as [LoadDate] From [$(vQVDRoot)\Archive_2018.xlsx] (ooxml, embedded labels, table is [$(vaSheetName1)]);
//Storing Total DB Summary data in a seperate table for furture dashboard reference STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary.qvd] (qvd);
STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary_$(vMonth).qvd] (qvd);
----------------------------------------------
2. Arriving the Summary for Nov-2018 based on the Resident Contacts data as below & need to insert into same qvd,
//Current Month Database summary loading //From [$(vQVDRoot)\Contacts_$(vMonth).qvd] (qvd) DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'GA' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [GA] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'AMERICAS' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [AMS] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'EMEA' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [EMEA] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'APAC' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where [APAC] = 'Yes' Group by [ContactMonth];
DatabaseSummaryTemp: LOAD [ContactMonth] as [dbMonth], 'ALL OTHERS' as [dbDetails], count([Email Address]) as [dbCount] Resident Contacts Where Not [GA] Like '*Yes*' And Not [AMS] Like '*Yes*' And Not [EMEA] Like '*Yes*' And Not [APAC] Like '*Yes*' Group by [ContactMonth];
DatabaseSummary: Load [dbMonth]&'-'&[dbDetails] as PrimaryKey, [dbMonth], [dbDetails], [dbCount],Date(Now()) as [LoadDate] Resident DatabaseSummaryTemp;
3. loading existing data from QVD & Concatenating with Nov-2018 data,
Concatenate(DatabaseSummary) LOAD ltrim(rtrim([dbMonth]&'-'&[dbDetails])) as Primarykey, [dbMonth], [dbDetails], [dbCount], [LoadDate] FROM [$(vQVDRoot)\DatabaseSummary.qvd] (qvd);
STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary.qvd] (qvd);
STORE DatabaseSummary INTO [$(vQVDRoot)\DatabaseSummary_$(vMonth).qvd] (qvd);
-------------------------------
4. But when i reload Nov-2018 it is adding as new record into qvd & it is not replacing the Nov-2018 record. Keeps on increasing the Nov-2018 data inside the qvd.
I have noticed that your code is not checking for pre-existing records.
Try.
DatabaseSummary: Load [dbMonth]&'-'&[dbDetails] as PrimaryKey, [dbMonth], [dbDetails], [dbCount], Date(Now()) as [LoadDate] Resident DatabaseSummaryTemp;
/*********************************************************************************************************/ Concatenate(DatabaseSummary) LOAD ltrim(rtrim([dbMonth]&'-'&[dbDetails])) as Primarykey, [dbMonth], [dbDetails], [dbCount], [LoadDate] FROM [$(vQVDRoot)\DatabaseSummary.qvd] (qvd) Where not Exists(PrimaryKey, ltrim(rtrim([dbMonth]&'-'&[dbDetails])))
