Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

loop through data to get all variations.

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

0105101061854-890-Servicemen
0120501205850-852-Servicemen
0120501205854-890-Servicemen
0100801009853-853-Materials and consumables
0100801009901-928-Materials and consumables
0100801009931-944-Materials and consumables
0100801009905-913-09Materials and consumables
0100801009957-957-Materials and consumables
0100801009963-981-Materials and consumables
0101201012853-853-Materials and consumables
0101201012901-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? .

1 Solution

Accepted Solutions
Jason_Michaelides
Partner - Master II
Partner - Master II

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

View solution in original post

14 Replies
Jason_Michaelides
Partner - Master II
Partner - Master II

Hi,

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

Jason

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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
Partner - Specialist III
Partner - Specialist III
Author

Any help on my initial question would be much appreciated

Jason_Michaelides
Partner - Master II
Partner - Master II

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

Jason

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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
Partner - Master II
Partner - Master II

But what does that row even mean!?

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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
Partner - Master II
Partner - Master II

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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