Duplicate Numbers but not rows

racer25 — Tue, 10 Jul 2018 14:28:37 GMT

Hi All,

Kind of an unusual one ( or I hope not ). Our internal expense system is only able to output a flat file for a full extract of all claims but that creates duplicate numeric values as effectively its a one to many relationship in the extract.

Report ID

EMPLOYEE_ID

Status

Created

Date

Submit Dt

Updated

SetID

Line

Line Status

Type

Trans Date

Amount

Currency

Tran Amt

Currency

User Rate

Multiplier

Divisor

Rate Type

Location

Merchant

Long Descr

No Rcpt

Name

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 1

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 2

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 3

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 4

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 5

Report ID

EMPLOYEE_ID

Status

Created

Date

Submit Dt

Updated

SetID

Line

Line Status

Type

Trans Date

Amount

Currency

Tran Amt

Currency

User Rate

Multiplier

Divisor

Rate Type

Location

Merchant

Long Descr

No Rcpt

Name

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 1

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 2

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 3

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 4

1566258

123456

29/01/2014

08/07/2015

19/12/2014

15/07/2015

COMPANY

CLNTENT

21/11/2013

EUR

DAY

DUBLN

Food Place

Client

Person 5

Above is initial file 5 (rows) and below that is the same recordset and how I would like it to appear.

Report ID "1566258" is unique

Line "2" is unique to the duplicated line items for that claim only.

The last column contains the differing data which needs be retained.

I am not averse to doing a count of a concatenated Report ID and Line and dividing Trans Amount by Count ? That would also be acceptable to have 16.8 on 5 rows but the preferable outcome is one row with 84.

I'd appreciate some guidance on how to complete.

Obviously in a perfect world I'd have decent data to start with but I can't fix that so on to plan B.

Thanks in advance,

Rob

Re: Duplicate Numbers but not rows

neelamsaroha157 — Tue, 10 Jul 2018 15:28:18 GMT

Check this

Re: Duplicate Numbers but not rows

racer25 — Wed, 11 Jul 2018 08:16:03 GMT

Hi Neelam,

Thanks for taking a look at.

From your file I can't see an output like the second table I have show with zero for the subseuent records - have I missed something?

Thanks,

Rob

Re: Duplicate Numbers but not rows

neelamsaroha157 — Wed, 11 Jul 2018 15:49:17 GMT

The output is not exactly like the second table but instead of replacing all '84' value, I simply created another table with the data you needed in the second table. Is there any specific reason you want to replace the value with '0'?

topic Re: Duplicate Numbers but not rows in QlikView

Duplicate Numbers but not rows

Re: Duplicate Numbers but not rows

Re: Duplicate Numbers but not rows

Re: Duplicate Numbers but not rows