Reverse order in QVD

[nachiket_shinde]

Hello Friends,

I have  QVDs for each day which has following data(userdata_20170314 to userdata_20190314.qvd)

ID	user1	user2	user3	user4	user5
1	A	B	C	D	E
2	A	B	C
3	A	B
4	A	B	C	D
5	A

I am not sure for which ID how many users may have value.

For QVDSfrom 'userdata_20170314' to 'userdata_20180314' , i want to change data as per below

ID	user1	user2	user3	user4	user5
1	E	D	C	B	A
2	C	B	A
3	B	A
4	D	C	B	A
5	A

In short we are reversing all values.

Please suggest.

Thanks in advance

[Vegar · Accepted solution]

It is a bit tricky, but this script will do the trick for rading your special period files.

OUTPUT:
LOAD
  ID,
  SubField(Users, '|', -1) as user1,
  SubField(Users, '|', -2) as user2,
  SubField(Users, '|', -3) as user3,
  SubField(Users, '|', -4) as user4,
  SubField(Users, '|', -5) as user5
  ;
LOAD 
 ID, 
  user1 &if(len(user2)>0,'|'&user2)&if(len(user3)>0,'|'&user3)&if(len(user4)>0,'|'&user4)&if(len(user5)>0,'|'&user5) as Users
FROM
  SOURCEDATA;

[nachiket_shinde]

This is working as expected.

Thanks a lot for your help.

[nachiket_shinde]

Hello Vegar,

I have another situation here.

What if my data is as below

ID	user1	user2	user3	user4	user5
1	A	-	B	C	D
2	C	D	-	Q	P
3	A	B	C
4	S	-	C	-	B

And expected is

ID	user1	user2	user3	user4	user5
1	D	C	B	-	A
2	P	Q	-	D	C
3	C	B	A
4	B	-	C	-	S

Thanks in advance.