Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

krisjanis
Not applicable

Incremental load with update

Hi,

I have an excel with old data sheet (OLD):

OrderId-RevenueOrderIdRevenueOther
1-101101
1-101102
1-10011003
2-5252
2-102102
2-152153
2-202204
3-503501
4-504501
5-505501

And new excel with new data sheet (NEW)

OrderId-RevenueOrderIdRevenueOther
1-1000110002
2-5251
2-5253
6-606601

1st I would like to get result:

All new data and something from old table that matches with order-revenue:

OrderId-RevenueOrderIdRevenueOther
1-1000110002
2-5251
2-5253
6-606601
2-5252

Last row is from old table, because 2-5 matches with new table.

Next, what i want is to do is do calculation, summarize 2-5 rows and the update old table with new:

OrderId-RevenueOrderIdRevenueOther
1-1000110002
6-606601
2-52156

Full table:

Old sheet + new calculated sheet

OrderId-RevenueOrderIdRevenueOther
1-101101
1-101102
1-10011003
2-102102
2-152153
2-202204
3-503501
4-504501
5-505501
1-1000110002
6-606601
2-52156

Can you  help me please?

1 Solution

Accepted Solutions
krisjanis
Not applicable

Re: Incremental load with update

I figured out myself:

table1:

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is New);

//where not exists([OrderId-Revenue]);

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is Old)

where exists([OrderId-Revenue]);

NoConcatenate

table2:

load

[OrderId-Revenue],

OrderId,

sum(Revenue) as Revenue,

sum(Other) as Other

Resident table1

group by [OrderId-Revenue],OrderId ;

drop tables table1;

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is Old)

where not  exists([OrderId-Revenue]);

1 Reply
krisjanis
Not applicable

Re: Incremental load with update

I figured out myself:

table1:

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is New);

//where not exists([OrderId-Revenue]);

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is Old)

where exists([OrderId-Revenue]);

NoConcatenate

table2:

load

[OrderId-Revenue],

OrderId,

sum(Revenue) as Revenue,

sum(Other) as Other

Resident table1

group by [OrderId-Revenue],OrderId ;

drop tables table1;

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is Old)

where not  exists([OrderId-Revenue]);