topic Re: Update Rows is not working in load script in App Development

Update Rows is not working in load script

nandhaadjame200 — Fri, 16 Nov 2018 10:20:33 GMT

Hi There

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

Thanks

Nandhakumar

Re: Update Rows is not working in load script

nandhaadjame200 — Fri, 16 Nov 2018 10:28:38 GMT

To add more details & Steps i'm following is,

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

--------------------------------------------------------

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.

Thanks

Nandhakumar

Re: Update Rows is not working in load script

vamsee — Fri, 16 Nov 2018 16:43:55 GMT

Hello,

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

;

The rest of the script looks okay.

Re: Update Rows is not working in load script

vamsee — Fri, 16 Nov 2018 16:44:46 GMT

This might help.

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/