topic Reordering table in QlikView

Reordering table

Tue, 07 Jul 2009 21:20:28 GMT

Hello!

I'm in trouble with the new format of a report that a telco sends us monthly. Formerly it used to be like this:

user1 info1
user1 info2
user1 info3
user2 info4
...

Now they changed the format as follows:

04 user1
05 info1
05 info2
06 info3
04 user2
05 info4
...

I need to bind every detail row (marked by row header 05) to its header (marked by row header 04), but I can't make it. I'm not that good in QlikView, as you can guess...

Is there anyone who can help, please?

Thanks,
Luca

Reordering table

stephencredmond — Tue, 07 Jul 2009 22:06:39 GMT

Hi Luca,

How about this:


Load
 If (Code='04', Value, Peek('User', -1)) As User,
 if (Code='05', Value, Null()) As Info
;
Load * Inline [
Code,Value
04,user1
05,info1
05,info2
06,info3
04,user2
05,info4
]

Stephen

Reordering table

Tue, 07 Jul 2009 22:22:30 GMT

I think a simple peek() function (in this case previous() will also work) will do the trick.

Here is sample script code based on your example:


T1:
Load
 If(RecType='04',Value,peek(User)) as User, // IF NOT RECTYPE=04 THEN USE PREVIOUS RECORD'S VALUE
 If(RecType='05',Value) as Info_Value; // ONLY STORE RECTYPE=05 VALUES
Load * Inline [
RecType,Value
04, user1
05, info1
05, info2
06, info3
04, user2
05, info4];

Reordering table

stephencredmond — Tue, 07 Jul 2009 22:30:30 GMT

Hi PJB,

Just note that your peek is missing the single quotes (that one has often got me banging my head against a wall!).

In this case, Previous will not work because the value will not always be populated in the source data. The Peek makes sure that it will be populated because we have populated it.

Regards,

Stephen

Reordering table

Wed, 08 Jul 2009 20:54:24 GMT

Thank you, Stephen and pjb, for your help! I think you put me on the right way to the solution: I'm working for the details, then I'll mark the question as solved. Thanks!

Reordering table

Wed, 08 Jul 2009 21:04:11 GMT

Got it work! Thanks a lot for the help!