topic How to make data linear? in QlikView

How to make data linear?

Thu, 09 Dec 2010 22:43:52 GMT

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

How to make data linear?

Thu, 09 Dec 2010 23:23:50 GMT

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;

How to make data linear?

Thu, 09 Dec 2010 23:45:05 GMT

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!!

How to make data linear?

Fri, 10 Dec 2010 00:34:49 GMT

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;

How to make data linear?

Fri, 10 Dec 2010 20:33:06 GMT

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

How to make data linear?

Sat, 11 Dec 2010 17:21:40 GMT

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

Regards

//Nils

How to make data linear?

Sat, 11 Dec 2010 22:06:02 GMT

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

How to make data linear?

Wed, 15 Dec 2010 19:50:03 GMT

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

How to make data linear?

Thu, 16 Dec 2010 22:30:28 GMT

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;

How to make data linear?

Thu, 16 Dec 2010 22:52:36 GMT

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.

How to make data linear?

Fri, 17 Dec 2010 13:29:35 GMT


Then use a flag instead

temp:
load *,if(peek('ID')<>ID,'1') as flag resident [DAILY TABLE] order by ID,recnum desc;


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