Joins

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement. I have two source files named Original Records input file and Change Records input file

The Original Records input file contains the Actual data. The change Record file contains the changes on Original records file rows at different time stamps.

Daily some of the old records will be updated with new records on different time slots. From those need to pick only Max changed date time records and need to keep the not changed records same as it is based on Staff no, ID and Account Category fields .

I am adding more detail in image.Please find the below screen shot.

Please help me on this logic.

Re: Joins

felipedl — Tue, 07 Nov 2017 13:19:46 GMT

Hi bhavani,

Try the script below.

// Just date transformation from Excel file

tmp:

LOAD [Staff No],

ID,

[Account Category],

date#([Changed Date and Time],'DD-MM-YYYY hh:mm:ss') as [Changed Date and Time]

FROM

(ooxml, embedded labels, table is sheet1);

// New records from Excel file

dataNew:

LOAD [Staff No],

FY,

name,

ID,

[Group Name],

[Account Category],

[Client Coverage],

[Role Type],

[Old Revenue Target],

[Old Sales Target],

[Old Pen Target],

[Old Margin Target],

[Revenue Target],

[Sales Target],

[Margin Target],

[Pen Target],

date#([Changed Date and Time],'DD-MM-YYYY hh:mm:ss') as [Changed Date and Time]

FROM

(ooxml, embedded labels, table is sheet1);

// Gets max date/time from records based on Staff No, ID and Account Category

NoConcatenate

maxChangeDateTime:

Load

[Staff No],

ID,

[Account Category],

max([Changed Date and Time]) as [Changed Date and Time]

Resident tmp

group by [Staff No], ID, [Account Category] ;

drop table tmp;

// Join to see which records must be put in the original file

left join(dataNew)

Load

[Staff No],

ID,

[Account Category],

[Changed Date and Time],

1 as [Flag New Records]

Resident maxChangeDateTime;

// Data from Original records

data:

LOAD [Staff No],

FY,

name,

ID,

[Group Name],

[Account Category],

[Client Coverage],

[Role Type],

[Old Revenue Target],

[Old Sales Target],

[Old Pen Target],

[Old Margin Target]

FROM

(ooxml, embedded labels, table is Sheet1);

// Checks which records must be replaced

left join (data)

Load

[Staff No],

ID,

[Account Category],

[Changed Date and Time]

Resident maxChangeDateTime;

NoConcatenate

finalTable:

Load

Resident data

where IsNull([Changed Date and Time]);

Concatenate(finalTable)

Load

Resident dataNew

where [Flag New Records]=1;

drop tables maxChangeDateTime,data,dataNew;

Should give you the following image:

topic Joins in QlikView

Joins

Re: Joins