topic Version Number in QlikView

Version Number

Thu, 19 Apr 2012 15:52:58 GMT

Hi Experts,

(I have touched on this as part of another thread also - Distinct compare 2 diff tables in load)

Quick one, I have a table that will have duplicate entries within 1 of the fields, what I wish to do is count what version it is so I may concatenate it together to ensure I only compare correct timeseriesed accounts - see tables below please

Original :

Account Version Number

1234 1

1111 1

2222 1

1234 2

1111 2

1234 3

2222 2

If I was doing this in excel I would count(A$2:A2,A2) so I would count from the start to where I am now then Id concatenate account and Version the desired result is below ( I know theres a recno() function, is there an equivalent verno(Account) similar)

Desired result:

Account Version Number Search

1234 1 12341

1111 1 11111

2222 1 22221

1234 2 12342

1111 2 11112

1234 3 12342

2222 2 22222

Can anyone help please ?

Thanks

Version Number

kaushiknsolanki — Thu, 19 Apr 2012 16:47:21 GMT

Hi,

Can you please tell us the logic..

Cause in your example only Accout which has version number 3 is concated with older version.

So pls tell whats the logic.

Regards,

Kaushik Solanki

Re: Version Number

jonathandienst — Thu, 19 Apr 2012 18:18:04 GMT

Please check out the attached file. Does it do what you want?

// Load source data. RowNo is used to maintain original load order

Data:

LOAD

Account,

RowNo() As RowNo

INLINE

[

Account

1234

1111

2222

1234

1111

1234

2222

];

// Calculate the sequence number by looping over the data and incrementing

// the sequence number for successive accounts with the same value

Sequence:

LOAD *,

Account & Sequence As SearchNo

;

LOAD Account,

RowNo,

If(Account = Previous(Account), Peek('Sequence') + 1, 1) As Sequence

Resident Data

Order By Account, RowNo;

DROP Table Data;

Regards

Jonathan

Version Number

rwunderlich — Fri, 20 Apr 2012 01:09:40 GMT

If you want to add sequence numbers to the accounts, I find the easiest way is to add this field in the load:

autonumber(recno(), Account) as [Version Number]

-Rob

Version Number

Fri, 20 Apr 2012 08:25:12 GMT

Thanks Guys

Think I have it going on