Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Incremental Load -- Identify type

Hi Communities,

I would like to extract the modified data only between Table A and Table  by using incremental load. In addition, I would like to  identify the data, and create a new column called "Modified Type", which includes "Add", "Modified" and "Deleted". (

Please refer below information.

Thanks in advanced.

Table A:

Order NumberProduct IDSales NumberUnit PriceSales AmountOrder Date
4083891071325230752017/1/26
400887891245654722017/2/1
408749065347225552017/6/12
409702426211470682017/2/6
40204548133332017/4/9
4000121056282157922017/6/29
4040569970453317102017/4/18
4090582090211189902017/2/25
40845313725594352017/4/24
4080776844332146082017/2/16
40759483330199332017/6/14

Table B:

Order NumberProduct IDSales NumberUnit PriceSales AmountOrder Date
4083891071325230752017/1/26
400887891245654722017/2/1
408749075347260252017/8/12
409702428211493482017/7/14
40204548133332017/4/9
4000121056282157922017/6/29
4040569970453317102017/4/18
4090582090211189902017/2/25
4080776844332146082017/2/16
40759483330199332017/6/14
40978312125553552017/7/13

Output:  

Order NumberProduct IDSales NumberUnit PriceSales AmountOrder DateModified Type
408749075347260252017/8/12Modified
409702428211493482017/7/14Modified
40845313725594352017/4/24Deleted
40978312125553552017/7/13Add
3 Replies
Highlighted
Master
Master

Re: Incremental Load -- Identify type

Why do you want to use incremental load on this?

Which field do you wamt to keep reference for incremental load?

You can simple use where not exist(Table_name, Field_name)

Highlighted
Partner
Partner

Re: Incremental Load -- Identify type

Hi Kate Tsan,

Use attached qvw file. Hope it helps.

Thanks,

Yadhu

Highlighted
Creator
Creator

Re: Incremental Load -- Identify type

Hi Sirs,

Using join expression to compare two tables, but not sure the efficiency with big data set.

Thanks for your advised.

Modified Data.png

//************************* Identify "Add" or "Modified" ************************* //

[Add Table]:

LOAD

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date]

FROM [Data\Incremental Reload.xlsx] (ooxml, embedded labels, table is 工作表2)

Where [Order Date]>= '2017/07/01'

;


[Initial Table]:

Left Join Load

[Order Number],

'A' as Temp

From [Output\qvd files\IncrementalReload_Initial_P2.QVD](qvd)

;


[Final_Add Table]:

Load

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date],

if(Temp = 'A' and [Order Date]>= '2017/07/01', 'Modified', 'Add') as [Modified Type]

Resident [Add Table]

;

Drop Table [Add Table]

;


//************************* Identify "Deleted" ************************* //

[Initial Table]:

Load

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date]

From [Output\qvd files\IncrementalReload_Initial_P2.QVD](qvd)

;


[Delete Table]:

Left Join LOAD

[Order Number],

'D' as [Flag]

FROM [Data\Incremental Reload.xlsx] (ooxml, embedded labels, table is 工作表2)

;


[Final_Delete Table]:

Load

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date],

if([Flag] = 'D', '', 'Delete') as [Modified Type]

Resident [Initial Table]

Where [Flag] <> 'D'

;

Drop Table [Initial Table]

;