Creating index column based on number of rows

[#1]

Hi ya'll!

I've got a huge QVD (100+ milion rows) that looks something like this:

Personal nr	Case nr	.........	Date
123456-1234	111	.........	2013-12-09
123456-1234	111	.........	2013-12-09
123456-1234	222	.........	2013-12-09
987654-4321	333	.........	2013-06-01
987654-4321	333	.........	2013-11-23
112233-4455	444	.........	2013-12-09

At Load I'm trying to get it to look something like this:

Personal nr	Case nr	.........	Date	Index
123456-1234	111	.........	2013-12-09	1
123456-1234	111	.........	2013-12-09	2
123456-1234	222	.........	2013-12-09	1
987654-4321	333	.........	2013-06-01	1
987654-4321	333	.........	2013-11-23	2
112233-4455	444	.........	2013-12-09	1

So, I want Qlik to look at Case nr and see if there's more then one row for each Case nr and do a index of the cases. For example, case 111 has two rows. Then Qlik should put a '1' in the first occurence and a '2' in the second. For case 222 there's only one occurence, and it should just put a '1' at the Index place.

The reason I want to do this is to be able to run this and generate a new QVD that only has the 1's included in it. That should reduce the huge amount of data to a more manageable dataset.

Appreciate all the help I can get 🙂

Regards

Johan

[Anonymous]

Hi,

You  might as well try the exists function.

Something like below.

Load A, B, C, from Employees.csv where not exists (A);
This is equivalent to performing a distinct load on field A. 

hope this helps.

Regards,
Aadil

[#3]

I'm afraid that wont work as I'm afraid I'll need the entire dataset in the future.

And with a distinct load on field A I wont have any knowledge of the entries removed. I need to be able to sort it by the index number.

[Anonymous]

Johan

You could use the peek() function to see if the Case nr from the previously loaded row is the same as the current row.

If not the same

• Set your Index to 1

If it is the same

• Peek() the Index from the previous row
• Add 1 to it
• Set you index to this value

This assumes your data arrives in the correct order, if not then you 'll have to load it into resident and then sort it.

Best Regards,     Bill

[#5]

Hey Bill!

Now we're talking! That's something I'm thinking of aswell.

Could you give me a codeexample perhaps?

/Johan

[Anonymous]

Johan

Have a look at this Technical Brief: Generating Missing Data In QlikView. by hic especially pages 6 & 7 of the pdf Generating missing data.pdf especially pages 6 & 7.

Best Regards,      Bill

[Anonymous]

Johan

I just posted some usefull links but they have got bogged down in the Moderator quagmire.

You need to search this forum for a Technical Brief called

• Generating Missing Data In QlikView

Which should have this pdf attached

• Generating missing data.pdf

Pages 6 & 7 explain peek, but whole pdf is worth a read.

Best Regards,      Bill

[#8]

try this:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/16/counters-in-the-load

[hic]

The file that Bill talks about is found on:

Generating Missing Data In QlikView

HIC

[jagan]

Hi,

Please try script below for solution

Data:

LOAD *

, If(Previous(Year) = Year AND Previous(Product) = Product, Peek('Row#') + 1, 1) AS Row#

INLINE [ 

    Year, Product

    2011, A

    2011, A

    2011, B

    2012, A

    2012, B

    2013, A

    2013, A

    2013, A

    2013, C   

];

Regards,

Jagan.