Filmography of Rajinikanth and Qlikview carriage return

Hello everyone! Hope you are enjoying your journey with Qlikview. Yesterday I had posted Prakash raj's filmography to learn various features of charts. Today lets see Rajini's filmography which covers other features. 

Super Star defines it all! :D 














Data Used: http://en.wikipedia.org/wiki/Rajinikanth_filmography

In short: 
Carriage return in a field? Chr(13) is the character for carriage return. 
Replace(TextWithCarriageReturn,chr(13),'') while loading or follow the below method.

Script: 

When we load Prakash sir's filmography we used for loop to load different tables available in the same url. 

Below that we have loaded Rajini's filmography URL. This URL has just one table. 

We include a new column Person to differentiate the data of the two actors.

When we reload it and see a table view we see the bwlo (ctrl+T) for table view: 

It is generally practiced to avoid synthetic key. The reasons will be discussed in the future chapters.

How to do this? Add the word concatenate between the two load.

The resulting table view will have just one table with both the actors data as below: 

Previously to find the awards we used the below expression 

=count({}Film)

But in this case let's check the date first: 

Here we have three lines in the same field with carriage return. Only if we consider the three we can get the right number, right? If we use the above expression with notes=*Award* 2010 will return just 1 as the award number. 

So we use this expression 
=substringcount(if(SubStringCount(concat(Notes),'Award')>0,concat(Notes)),'Award'))

For any given year, Concat(Notes) will concatenate all the notes into one string. Say for 2010 the notes will have "Award1 Award2 Award3" as a result of Concat(Notes)

Now, dealing with the subexpression highlighted below. Substringcount function will return the number of times 'Award' has appeared in the concat(Notes) so in our case 3. IF this number is >0 we return concat notes to be dealed with the outer sustringcount.

The outer substringcount just gives the number of times Award has appeared in the concat(Notes) so we get the number of awards.

Let us again take a look at the example record we have chosen.

Here the number of awards in 2010 is 2 but the number of times 'Award' has appeared is 3.
Which means we dont need to take Nominations into account.

So the final Expression will be as below. Calculating number of times Award appears in a Concat(Notes) subtracting number of times nominated appears in it.

The final Dashboard! 

Our super star has done films in 7 languages. 

Won 9 awards. 

112 films in Tamil. 

21 movies in 1978 alone! Must have been a very busy year for him.