Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
omyahamburg
Contributor II

How to tranform table/generate data

I have a table with entry nos. which refer to a "from entry" and "to entry" numbers.

It looks like this:

No.From entry No.To entry No.
9911617780731778084
9911717780851778088
9911817780891778092

I like to transform the data to get the result like follows (I had a look into "Generating missing data", but failed).

No.From Entry No.To Entry No.
9911617780731778084
9911617780741778084
9911617780751778084
9911617780761778084
9911617780771778084
9911617780781778084
9911617780791778084
9911617780801778084
9911617780811778084
9911617780821778084
9911617780831778084
9911617780841778084
9911717780851778088
9911717780861778088
9911717780871778088
9911717780881778088
9911817780891778092
9911817780901778092
9911817780911778092
9911817780921778092

Attached is a sample qvw, it would be great if someone could assist me.

Joerg

1 Solution

Accepted Solutions
omyahamburg
Contributor II

Re: Re: How to tranform table/generate data

Thank you all for your help.

I solved it now, pls see attachment if of interest.

13 Replies
vikasmahajan
Honored Contributor III

Re: How to tranform table/generate data

REQUIREMENT IS NOT CLEAR WHAT U WANT TO ACHIEVE ?

VIKAS

omyahamburg
Contributor II

Re: How to tranform table/generate data

Dear Vikash

E.g. No. 99116 the values "From Entry No." and "To Entry No." are 1778073 and 1778084,

so I want to create the table for this No. as follows:

No.      From Entry No.      To Entry No.

99116 1778073                1778084                this is the original data, all following are to be created

99116 1778074                1778084

99116 1778075                1778084

99116 1778076                1778084

99116 1778077                1778084

99116 1778078                1778084

99116 1778079                1778084

99116 1778080                1778084

99116 1778081                1778084

99116 1778082                1778084

99116 1778083                1778084

99116 1778084                1778084

So in principle to create for each No. the field "From entry No." progressive until the value of "To entry No." is reached.


vikasmahajan
Honored Contributor III

Re: How to tranform table/generate data

I still not clear what exact you want to achieve ?

Not applicable

Re: How to tranform table/generate data

Hi Jörg,

I would use the ITERNO() function that starts at 1 and let you do a loop for each line of the table

sth like :

LOAD No, FromEntry + iterno() as FromEntry, ToEntry

From xxx

WHILE  FromEntry + iterno() <= ToEntry

Fabrice

omyahamburg
Contributor II

Re: How to tranform table/generate data

Dear Aunez

This works perfect !

Thanks a lot.

Joerg

Not applicable

Re: How to tranform table/generate data

hi

try this code and see attachment

LOAD *,

IterNo() as i,

([From entry No.]+IterNo()) as [From entry No new],

([From entry No.]+IterNo()) as [To entry No new]

Inline [

No.,    From entry No.,    To entry No.

99116,    1778073,    1778084

99117,    1778085,    1778088

99118,    1778089,    1778092

] While ([From entry No.]+IterNo())<=[To entry No.];

omyahamburg
Contributor II

Re: Re: How to tranform table/generate data

Also a good solution.

How can I manage that the first From Entry No. keeps untouched ?

No.From entry No.From entry No newTo entry No.To entry No new
991161778073177807317780841778074
991161778073177807417780841778074
991161778073177807517780841778075
991161778073177807617780841778076
991161778073177807717780841778077
991161778073177807817780841778078
991161778073177807917780841778079
991161778073177808017780841778080
991161778073177808117780841778081
991161778073177808217780841778082
991161778073177808317780841778083
991161778073177808417780841778084
kiranmanoharrode
Contributor III

Re: How to tranform table/generate data

Dear Jorg,

     PFA for solution,

Regards

Kiran Rode

+91 8976977897

Not applicable

Re: How to tranform table/generate data


Hi,

What do you mean with "untouched" ?

Iterno() starts at 1. You can do IterNo()-1 + xxx

Fabrice

Community Browser