Field to Column Ref & Update

[#1]

Hi,

I am currently trying to restructure data based upon a column where the fields give a reference to another column along with the value that needs to change.

The raw data looks like this (shrunk down and restructed to 1 Ref):

Ref	A1	A2	A3	A4	A5	DATE	TIME	NEW	OLD	FIELD	ORDER
9876	3325.5	0	1863	1900	1679.85	16/04/2013	12:30	6137.00	0	A2	1
9876	3325.5	0	1863	1900	1679.85	15/11/2013	09:23	11250.50	6137.00	A2	2
9876	3325.5	0	1863	1900	1679.85	15/11/2013	09:23	1863	0	A3	3
9876	3325.5	0	1863	1900	1679.85	15/11/2013	09:23	1679.85	0	A5	4
9876	3325.5	0	1863	1900	1679.85	20/11/2013	12:10	3325.50	11250.50	A2	5
9876	3325.5	0	1863	1900	1679.85	20/11/2013	12:10	1900	0	A4	6
9876	3325.5	0	1863	1900	1679.85	07/02/2014	13:09	0	3325.50	A2	7
9876	3325.5	0	1863	1900	1679.85	07/02/2014	13:09	3325.50	0	A1	8

A1 - A5 shows current data.  FILED refers to the field that needs to be changed and NEW and OLD refers to the new and old values.

The output should look like this:

RefN	A1N	A2N	A3N	A4N	A5N	DATEN	TIMEN	NEWN	OLDN	FIELDN	ORDERN
9876	0	6137	0	0	0	16/04/2013	12:30	6137.00	0	A2N	1
9876	0	11250.5	0	0	0	15/11/2013	09:23	11250.50	6137.00	A2N	2
9876	0	11250.5	1863	0	0	15/11/2013	09:23	1863	0	A3N	3
9876	0	11250.5	1863	0	1679.85	15/11/2013	09:23	1679.85	0	A5N	4
9876	0	3325.5	1863	0	1679.85	20/11/2013	12:10	3325.50	11250.50	A2N	5
9876	0	3325.5	1863	1900	1679.85	20/11/2013	12:10	1900	0	A4N	6
9876	0	0	1863	1900	1679.85	07/02/2014	13:09	0	3325.50	A2N	7
9876	3325.5	0	1863	1900	1679.85	07/02/2014	13:09	3325.50	0	A1N	8

I'm not too sure on the best way to achieve this, I was thinking about grouping and using long 'if' statements in the load script but im realy not convinced this is the best way.

Do anyone have ideas on the best way?

Thanks in advance.

[Gysbert_Wassenaar]

See attached qvw

[#3]

Thank you Gysbert, that looks perfect for what I am after.  Will now try to apply to the large dataset.

Thanks again.

[#4]

Hi Gysbert,

I have applied the method to my main application.  It works if I restrict the application to one reference number however if I use the mass data the results are mixed up.  I have thought about using a Group By function however it doesnt seem to work.

Any ideas?

Thanks

[Gysbert_Wassenaar · Accepted solution]

I think you probably need to make sure your data is sorted by Ref first and ORDERN second. Or maybe Ref, DATEN and TIMEN. And you might need a check to see you're dealing with the same Ref.

Change

     if(FIELD='A1',NEW,alt(peek('A1'),0)) as A1

to

     if(FIELD='A1',NEW, if(Ref=previous(Ref),peek('A1'),0)) as A1,

And the same for the other A- fields.