How to allign rows and columns

[Anonymous]

John Doe
ID	Date Bought	Product Bought
001	1/1/2011		Case 1
002	1/2/2011		Case 2
003	1/3/2011		Case 3
Mary Wu
ID	Date Bought	Product Bought
004	1/4/2011		Case 4
005	1/5/2011		Case 5
Linda Jones
ID	Date Bought	Product Bought
006	1/6/2011		Case 6
007	1/7/2011		Case 7
Joe White
ID	Date Bought	Product Bought
008	1/8/2011		Case 8
009	1/9/2011		Case 9
Jim Kenny
ID	Date Bought	Product Bought
010	12/31/2015		Case 10

I have the above table that needs rows and columns to be alligned  as in the post below:

ID	Date Bought	Last Name	Product Bought
001	1/1/2011	Doe	Case 1
002	1/2/2011	Doe	Case 2
003	1/3/2011	Doe	Case 3
004	1/4/2011	Wu	Case 4
005	1/5/2011	Wu	Case 5
006	1/6/2011	Jones	Case 6
007	1/7/2011	Jones	Case 7
008	1/8/2011	White	Case 8
009	1/9/2011	White	Case 9
010	12/31/2015	Kenny	Case 10

Thanks much appreciated

[Anonymous]

Can you explain the below code pls Digvijay‌ I would really appreciate. Many Thanks

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

[Digvijay_Singh]

Hi,

This code to transform table is auto-generated by Transformation Wizard, This specific code I think is generated when I deleted one of the blank row at top while transforming table through wizard.

[Anonymous]

what is your data source ?  inline load ? csv? Excel? if excel or csv then plz share that.....

[Anonymous]

HI everyone Thanks for the input. This is a sample file. My actual app has more data. I understand i need to use enable transformation wizard for this. Can you explain what step in that wizard removes empty coulmns row 5, 14, 19, 20, 21 and what step replaces John Doe, Mary Wu etc to a new column (see the attached image for this).

I want to understand how exactly this is done in the wizard, so pls help me out.

Thanks again

[Anonymous]

here is the excel file also

[Anonymous]

Digvijay, i worked with Enable Transformation wizard and I understand how you got the 1st line of code:

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))

but I did not understand how u got the remaining 2 lines:

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'ID'))),

Remove(Col, Pos(Top, 3))

Attached image shows how I got that line.It was not allowing me to remove another line as in your 2nd line.

can you pls explain what steps you took in the wizard. Thank you very much.

[Digvijay_Singh]

I tried to compile the steps in word doc, attaching here for your reference along with qvw, I am sure it will help you to handle your file if it is more or less same with the posted sample.

the script is as below, the script in bold below is manual script to take last name as you wanted and to format date and ID. -

I am not sure why but formatting ID and Date Bought wasn't working as it should be, so I had to give them different name, to keep the same name you can interchange them with T1 table.

T1:

LOAD @1 as ID,

     @2 as [Date Bought],

     @3 as [Product Bought],

     @4 as [Last Name]

FROM

[allign rows.xlsx]

(ooxml, no labels, table is Sheet2, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'ID'))),

Remove(Col, Pos(Top, 3)),

ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

Replace(4, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

NoConcatenate

Final:

Load

  Floor(ID) as ID1,

  Date(Floor([Date Bought])) as [Date Bought1],

  [Product Bought],

  Subfield([Last Name],' ',2) as [Last Name]

Resident T1;

Drop Table T1;

Hope it helps to manage your task.

[Digvijay_Singh]

The qvw attached here

[Anonymous]

Digvijay‌ Thank you so much for your elaborate instruction. Its all clear now.