How to make data linear?

[#1]

Below is the current structure of my data and i would like that to be changes into the structure that is posted below.

Date	Recnumber	To Status	From Status
12/1/2010	1	Valid	InValid
12/2/2010	1	Invalid	Valid
12/3/2010	1	Valid	Invalid
12/3/2010	1	Invalid	Valid
12/1/2010	2	Valid	Valid
12/1/2010	3	Valid	Invalid

I would like this to be changed into linear data in qlikview but not sure where to start.

Date	Recnumber	Status
12/1/2010	1	Valid
12/1/2010	1	InValid
12/2/2010	1	Invalid
12/2/2010	1	Valid
12/3/2010	1	Valid
12/3/2010	1	Invalid
12/3/2010	1	Invalid
12/3/2010	1	Valid
12/1/2010	2	Valid
12/1/2010	3	Valid
12/1/2010	3	Invalid

[#2]

If load order doesnt matter you can do:

load Date,Recnumber, mid([To Status],2) as Status resident xxx;

load Date,Recnumber, [From Status] as Status resident xxx;

[#3]

Nilsberg,

I am not looking to trim the field but rather make the record linear. If you notice, I have 4 records that is related to RecNum 1 whereas the desired table should have 8 records, It is almost like looking through a record as along as there is change in status for that RecNum.

Hope this makes sense.

Thank you!!

[#4 · Accepted solution]

Missread the table. Don't really get whats linear about the table either...

No big change of script though.

load Date,Recnumber, [To Status] as Status resident xxx;

load Date,Recnumber, [From Status] as Status resident xxx;

if you only like a record like:
"12/1/2010 2 Valid Valid"
to result in one line in the final table (seems like a likely typo). Add:

where [To Status]<>[From Status];

after the the last load statement.

If load order is important I would do the following:

temp:
load Date,Recnumber,recno() as recno,'1' as counter, [To Status] as Status resident xxx;

load Date,Recnumber,recno() as recno,'2' as counter, [From Status] as Status resident xxx;


load Date,Recnumber,Status; //think this will work. if not drop the counter and recno fields some other way.
noconcatenate load Date,Recnumber,Status,recno,counter
resident temp
order by recno,counter;

[#5]

Nilsberg,

Thank you much!! 🙂 Here is how i worked this issue

Thank you.

Pranita

test2:

load

Date as DATE2,

[Recnumber] AS [Recnumber2],

[From Status] AS STATUS,

recnum as recnum2

RESIDENT TEST

where [To Status] <> [From Status];

concatenate

load

Date as DATE2,

[Recnumber] AS [Recnumber2],

[To Status] AS STATUS,

recnum as recnum2

RESIDENT TEST

where [To Status] <> [From Status];

[#6]

You are most welcome. By the way, the 'concatenate' before the last load-statement is not necessary.

Regards

//Nils

[#7]

Maybe another solution :

data:
CrossTable(V , Status,2)
Load * Inline [
Date,Recnumber,To ,From
12/1/2010 , 1, Valid , InValid
12/2/2010 ,1 , Invalid , Valid
12/3/2010 ,1 , Valid , Invalid
12/3/2010 ,1 , Invalid, Valid
12/1/2010 ,2 , Valid , Valid
12/1/2010 ,3 , Valid , Invalid];

regards

[#8]

Nil,

I have one more issue that I am trying to resolve.

Below is my daily table. From this daily table, I only want to extract the max record based on recnum and all the corresponding data into separate table. How do i go about it? Any help will be appreciated.

DAILY TABLE
DATE	recnum	STATUS	ID	STATUS_BHANGE
11/3/2010	199701	A	1	A TO B
11/3/2010	199702	B	1	A TO B
11/3/2010	200711	B	1	B TO A
11/3/2010	200712	A	1	B TO A
11/23/2010	1554971	A	1	A TO B
11/23/2010	1554972	B	1	A TO B
11/23/2010	1563321	B	1	B TO A
11/23/2010	1563322	A	1	B TO A

MAX TABLE
DATE	recnum	STATUS	ID	STATUS_BHANGE
11/23/2010	1563322	A	1	B TO A

[#9]

Hi

There is a lot of ways you can do this. I would just go with a sort. If you dont like the extra column, just peek at the first record of the sorted table (here called temp) instead of using rowno.

hope it helps

//Nils

temp:
load *,rowno() as rowno resident [DAILY TABLE] order by recnum desc;

[MAX TABLE]:
noconcatenate load DATE,recnum,STATUS,ID,STATUS_BHANGE resident temp where rowno='1';
drop table temp;

[#10]

Nil,

Once again, I my senario isn't clear. Sorry about that. I am looking for the max record group by ID.

Meaning, for each ID there are multiple possible recnums. I need to pull only th max recnum for each ID and all the corresponding data to that recnum

Hope this explains better.

Thank you.