topic Re: Re: Re: How to tranform table/generate data in QlikView

How to tranform table/generate data

omyahamburg — Wed, 15 Jan 2014 09:09:20 GMT

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.
99116	1778073	1778084
99117	1778085	1778088
99118	1778089	1778092

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.
99116	1778073	1778084
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
99117	1778085	1778088
99117	1778086	1778088
99117	1778087	1778088
99117	1778088	1778088
99118	1778089	1778092
99118	1778090	1778092
99118	1778091	1778092
99118	1778092	1778092

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

Joerg

Re: How to tranform table/generate data

vikasmahajan — Wed, 15 Jan 2014 09:28:47 GMT

REQUIREMENT IS NOT CLEAR WHAT U WANT TO ACHIEVE ?

VIKAS

Re: How to tranform table/generate data

omyahamburg — Wed, 15 Jan 2014 09:41:04 GMT

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.

Re: How to tranform table/generate data

vikasmahajan — Wed, 15 Jan 2014 09:48:18 GMT

I still not clear what exact you want to achieve ?

Re: How to tranform table/generate data

Wed, 15 Jan 2014 09:52:13 GMT

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

Re: How to tranform table/generate data

omyahamburg — Wed, 15 Jan 2014 09:58:50 GMT

Dear Aunez

This works perfect !

Thanks a lot.

Joerg

Re: How to tranform table/generate data

Wed, 15 Jan 2014 10:22:49 GMT

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

Re: Re: How to tranform table/generate data

omyahamburg — Wed, 15 Jan 2014 10:34:51 GMT

Also a good solution.

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

No.	From entry No.	From entry No new	To entry No.	To entry No new
99116	1778073	1778073	1778084	1778074
99116	1778073	1778074	1778084	1778074
99116	1778073	1778075	1778084	1778075
99116	1778073	1778076	1778084	1778076
99116	1778073	1778077	1778084	1778077
99116	1778073	1778078	1778084	1778078
99116	1778073	1778079	1778084	1778079
99116	1778073	1778080	1778084	1778080
99116	1778073	1778081	1778084	1778081
99116	1778073	1778082	1778084	1778082
99116	1778073	1778083	1778084	1778083
99116	1778073	1778084	1778084	1778084

Re: How to tranform table/generate data

kiranmanoharrode — Wed, 15 Jan 2014 10:38:07 GMT

Dear Jorg,

PFA for solution,

Regards

Kiran Rode

+91 8976977897

Re: How to tranform table/generate data

Wed, 15 Jan 2014 10:40:30 GMT

Hi,

What do you mean with "untouched" ?

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

Fabrice

Re: Re: Re: How to tranform table/generate data

Vegar — Wed, 15 Jan 2014 10:48:21 GMT

You should use a -1 in your expression. Something like this:

LOAD  
  No. ,
  [From Entry No.]-1+ IterNo() as [From Entry No.] ,
  [To Entry No.]
INLINE [
No.,From Entry No.,To Entry No.
99116,1778073,1778084
99117,1778085,1778088
99118,1778089,1778092
]
WHILE ([From Entry No.]-1+ IterNo()<=[To Entry No.])
;

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

Re: Re: How to tranform table/generate data

omyahamburg — Wed, 15 Jan 2014 11:34:27 GMT

Thank you all for your help.

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

Re: Re: How to tranform table/generate data

Vegar — Wed, 15 Jan 2014 12:28:13 GMT

Jörg Hildebrandt: Great to hear that you got the help you needed. Please mark this issue as solved.

Re: How to tranform table/generate data

Wed, 15 Jan 2014 13:04:45 GMT

hello ,

please try dis.

LOAD * INLINE [

A1,B1,C1

99116,1778073,1778084

99117,1778085,1778088

99117,1778085,1778088

99118,1778089,1778092

];

u:

load min(A1) as g1 ,max(A1) as g2

Resident Test2;

let o=peek('g1');

let z=peek('g2');

for y= $(o) to $(z)

lo:

load A1 as A, min(B1) as T, max(C1) as K

Resident Test2

where A1=$(y)

group by A1;

let i=peek('A');

let m=peek('T');

let n=peek('K');

for j =$(m) to $(n)

Test:

LOAD

$(y) as P,

$(j) as q,

$(n) as v

AutoGenerate(1);

NEXT j;

drop Table lo;

NEXT y;

regards,

dattaram