Reordering table

[#1]

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

[stephencredmond · Accepted solution]

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

[#3]

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

[stephencredmond]

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

[#5]

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!

[#6]

Got it work! Thanks a lot for the help!