topic Re: loop through data to get all variations. in QlikView

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 09:12:28 GMT

Hi everyone.

I need help with writing a script to read through my data and generate a new table for all variations .

Here is a sample of how the data looks

CC From CC To EC From EC To Description

01051	01061	854-	890-	Servicemen
01205	01205	850-	852-	Servicemen
01205	01205	854-	890-	Servicemen
01008	01009	853-	853-	Materials and consumables
01008	01009	901-	928-	Materials and consumables
01008	01009	931-	944-	Materials and consumables
01008	01009	905-	913-09	Materials and consumables
01008	01009	957-	957-	Materials and consumables
01008	01009	963-	981-	Materials and consumables
01012	01012	853-	853-	Materials and consumables
01012	01012	901-	928-	Materials and consumables

So what i want to do is get all variations of CC From - CCTo in one field rather than 2.

the first line is 01051 01061, so in the new field i would have the following..

01051

01052

01053

01054

01055

01056

01057

01058

01059

01060

01061

Then loop through again to do line 2 etc etc

I need all of the relationships still to be there too, so when 01053 is selected, i still want to see EC details from 854-890 and for servicemen.

I want to do the same for ECFrom and ECTo but these fields contain additional characters.

905 to 913-09 , i would need a table containing

905

906

907

908

909

910

911

912

913-00

913-01

913-02

913-03

913-04

913-05

913-06

913-07

913-08

913-09

Can anyone help please? .

loop through data to get all variations.

Jason_Michaelides — Thu, 17 May 2012 09:35:23 GMT

Hi,

Check this solution here for someting similar - should get you on your way.

Jason

http://community.qlik.com/thread/50519

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 10:06:32 GMT

Thats what i want, but i am struggling to change it to my actual requirements, as i don't want date, but it seems that there are date functions in the example.

Sorry, i am still learning..

Chris

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 11:49:59 GMT

Any help on my initial question would be much appreciated

Re: loop through data to get all variations.

Jason_Michaelides — Thu, 17 May 2012 13:37:18 GMT

OK, I've gone through and replaced the relevant bits for you. I had to clean up the data slightly first.

Jason

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 14:04:50 GMT

Hi Jason, thats great, although i also need the extra bits on the end of some of the numbers i.e.

913-09

Youve stripped these out but we need them. thats the main thing i am struggling with

Thanks

Chris.

Re: loop through data to get all variations.

Jason_Michaelides — Thu, 17 May 2012 14:08:59 GMT

But what does that row even mean!?

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 14:13:45 GMT

To be honest i am not sure, all i know is that the client needs it as it is an expense code.

They use 913-09 in my example i have given, but there are also codes for:

913-08

913-07

913-06

etc.

Re: loop through data to get all variations.

Jason_Michaelides — Thu, 17 May 2012 14:26:42 GMT

Might numbers like that appear in both ECFrom and ECTo, or just ECTo?

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 14:27:45 GMT

as far as i have seen on what they have given me so far, it is just the to field.

Re: loop through data to get all variations.

Jason_Michaelides — Thu, 17 May 2012 16:04:25 GMT

Well that was a challenge! See attached. There are probably a few too many steps and the script could be smartened up a bit but you get the idea...

loop through data to get all variations.

hopkinsc — Thu, 17 May 2012 16:34:46 GMT

Thanks a lot Jason... I am just about to leave the office, but i will look at this at home and give you feedback tomorrow

loop through data to get all variations.

hopkinsc — Fri, 18 May 2012 08:18:48 GMT

Damn you are good!

Thanks very much for your help!!

loop through data to get all variations.

hopkinsc — Fri, 18 May 2012 09:44:34 GMT

Hi jason, Just a quick question on your script..

What does zi mean? I have seen it a few times in different examples..

As you can probably tell, i am still learning!

Thanks

Chris.

Re: loop through data to get all variations.

Jason_Michaelides — Fri, 18 May 2012 11:42:45 GMT

It's just a variable name - doesn't mean anything really! If you did a find and replace changing zi to dolphins it would still work. I thik "i" is a common numerator in programming parlance and zi has just made its way from one community post to another...