topic Re: Reverse order in QVD in QlikView

Reverse order in QVD

nachiket_shinde — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Reverse order in QVD

Vegar — Thu, 14 Mar 2019 12:50:36 GMT

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;

Re: Reverse order in QVD

nachiket_shinde — Mon, 18 Mar 2019 10:54:33 GMT

This is working as expected.

Thanks a lot for your help.

Re: Reverse order in QVD

nachiket_shinde — Tue, 26 Mar 2019 08:23:29 GMT

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.