loop through data to get all variations.

[hopkinsc]

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.

eg

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.

Eg

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? .

[Jason_Michaelides]

Hi,

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

Jason

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

[hopkinsc]

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

[hopkinsc]

Any help on my initial question would be much appreciated

[Jason_Michaelides]

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

Jason

[hopkinsc]

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.

[Jason_Michaelides]

But what does that row even mean!?

[hopkinsc]

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.

[Jason_Michaelides]

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

[hopkinsc]

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