How to do incremental load on Years based data

[Anonymous]

Hi all, I

have data like below in the example:

I have taken just for sample data like below, actual data having more fields and Dimensions are repeatative.

I want to load data from B-table excluding A-Table Years data.(here in the example i want to get 5-records from A table, 3-records from B-Table's 2014,2017 years data)

In my actual data i have much data but sample purpose i have given like this.

Can you please give code and or QVW file, that will help.

A:

Load * INLINE [

Year,Dept,Sales

2016,IT,20343

2011,Mec,545

2018,FA,32432

2016,FA,2343

2018,RnD,2343

]

Concatenate(A)

B:

Load * INLINE [

Year,Dept,Sales

2014,IT,20343

2016,Mec,545

2017,IT,32432

2017,FA,2343

2018,SA,2343,

2018,LD,3454

]

Want to get result as below:

Year,Dept,Sales

2016,IT,20343

2011,Mec,545

2018,FA,32432

2016,FA,2343

2018,RnD,2343

2014,IT,20343

2017,IT,32432

2017,FA,2343

Thanks,

Kumar

[dplr-rn]

Use not exists. See below

A:

Load * INLINE [

Year,Dept,Sales

2016,IT,20343

2011,Mec,545

2018,FA,32432

2016,FA,2343

2018,RnD,2343

];

Load * INLINE [

Year,Dept,Sales

2014,IT,20343

2016,Mec,545

2017,IT,32432

2017,FA,2343

2018,SA,2343,

2018,LD,3454

]

where not Exists(Year);

[chrismarlow · Accepted solution]

I thought not exists as well, but think it fails to pick up the second 2017 record, how about this;

A:
Load * INLINE [
Year,Dept,Sales
2016,IT,20343
2011,Mec,545
2018,FA,32432
2016,FA,2343
2018,RnD,2343
];

DistinctYear:
Load distinct
[Year]
resident A;

ConcatYears:
Load
Concat([Year],',') as Years
Resident DistinctYear;

Let Years=peek('Years');

Concatenate(A)
Load * INLINE [
Year,Dept,Sales
2014,IT,20343
2016,Mec,545
2017,IT,32432
2017,FA,2343
2018,SA,2343
2018,LD,3454
] where match([Year],$(Years))=0;

drop table DistinctYear, ConcatYears;

Or am I over complicating?

Regards,

Chris.

[Anonymous]

Hi Dilip,

it is giving the below result:

Year	Dept	Sales
2016	FA	2343
2018	FA	32432
2014	IT	20343
2016	IT	20343
2017	IT	32432
2011	Mec	545
2018	RnD	2343

but i want like below: (missing 2017 record from B-Table)

Year,Dept,Sales

2016,IT,20343

2011,Mec,545

2018,FA,32432

2016,FA,2343

2018,RnD,2343

2014,IT,20343

2017,IT,32432

2017,FA,2343

Please check another method if you get idea.

Thanks,

Kumar

[dplr-rn]

Good point i always forget that about no exists

[Anonymous]

you are correct 🙂

Thank you Chris.

it is working, Good.

No doubt working perfectly, but let us think easy one also if we get any.

Thanks,

Ravi

[chrismarlow]

If it is slow on your set of data you can try an approach with joining a flag;

A:
Load * INLINE [
Year,Dept,Sales
2016,IT,20343
2011,Mec,545
2018,FA,32432
2016,FA,2343
2018,RnD,2343
];

DistinctYear:
Load distinct
[Year]
resident A;

B:
NoConcatenate
Load * INLINE [
Year,Dept,Sales
2014,IT,20343
2016,Mec,545
2017,IT,32432
2017,FA,2343
2018,SA,2343
2018,LD,3454
];

left join (B)
Load
[Year],
1 as flag
Resident DistinctYear;

Concatenate (A)
Load
Year,
Dept,
Sales
Resident B
Where isnull(flag);

drop tables DistinctYear, B;

Cheers,

Chris.