topic Assigning number to text expression in Script in QlikView

Assigning number to text expression in Script

Wed, 28 Jul 2010 23:23:22 GMT

Hi,

I am working with the following script:

[Table A]:
LOAD
Year as YEAR,
Indicator_Name AS Series,
Values AS Fact
FROM
[Test.csv]

The Table looks a bit like this:

Year Indicator_Name Values
2000 IndicA 50
2001 IndicA 60
2002 IndicA 70
2000 IndicB 50
2001 IndicB 60
2002 IndicB 70
2000 IndicC 50
2001 IndicC 60
2002 IndicC 70

What I would like to achieve, is to add a column with a Indicator_key, like this:

Year Indicator_Name Values Indicator_Key
2000 IndicA 50 1
2001 IndicA 60 1
2002 IndicA 70 1
2000 IndicB 50 2
2001 IndicB 60 2
2002 IndicB 70 2
2000 IndicC 50 3
2001 IndicC 60 3
2002 IndicC 70 3

Now the tricky part is, I don't want to put the explicit texts of the Indicator_Name field in IF statements in my script.
Rather, I am looking for a mechanism that will assing the Key 1 to the first Indicator_Name occurence, then, give the same Key to all the subsequent Name's that are the same as the first one, and add +1 to the Key for each new Name. I would do this on a list pre-sorted by Indicator name.

What are your thoughts on doing this?

Thanks!

Assigning number to text expression in Script

Thu, 29 Jul 2010 01:25:24 GMT

If the list is presorted by Indicator name, you could use the following load statement:

[Table A]:
LOAD
Year as YEAR,
Indicator_Name AS Series,
Values AS Fact,
if(rowno()=1,1,if(peek(Indicator_Name)=Indicator_Name,peek(Indicator_Key),Peek(Indicator_Key)+1)) as Indicator_Key
FROM
[Test.csv]

Nimish

Assigning number to text expression in Script

Anonymous — Thu, 29 Jul 2010 05:07:59 GMT

Hey Tim,

If I understand the question correctly you want to generate an ID for every unique Indicator_Name. This can be done using the Autonumber function. Check the attached example document.

Assigning number to text expression in Script

Thu, 29 Jul 2010 05:19:48 GMT

Dear Johannes,

This is really neat. Never thought of this. Thanks for pointing this out.

Nimish

Assigning number to text expression in Script

Anonymous — Thu, 29 Jul 2010 05:31:55 GMT

You're welcome Nimish,

As you know, there are always several possible ways to get the job done in the script.

Assigning number to text expression in Script

Thu, 29 Jul 2010 10:43:58 GMT

Thanks guys!
Good to see it is much easier than I feared it would be.

Tonight I will try to rebuild the script to see if it does what I want it to do 🙂