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

[tamilarasu]

Hi,

Is this your excel file input.? If so, could you attach the excel file directly.?

[jagan]

HI,

Can you attach some sample data?

Regards,

Jagan.

[Digvijay_Singh · Accepted solution]

At least this works for your sample

T1:

LOAD @1 as ID,

    @2 as [Date Bought],

    @3 as [Product Bought],

    @4 as [Last Name]

FROM

[https://community.qlik.com/thread/209863]

(html, codepage is 1252, no labels, table is @1, filters(

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

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

Remove(Col, Pos(Top, 3))

));

NoConcatenate

T2:

Load

  ID,[Date Bought],[Product Bought],

  if(len([Date Bought])=0,subfield(ID,' ',2),peek([Last Name])) as [Last Name]

Resident T1;

NoConcatenate

Final:

Load *

Resident T2

Where len([Date Bought])>0;

Drop Table T1,T2;

[settu_periasamy]

One more way

Directory;
T1:
LOAD RecNo() as Rec,
@1,
@2,
@3,
@4
FROM
Book1.xlsx
(ooxml, no labels, table is Sheet1, filters(
Replace(3, right, StrCnd(null)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
Remove(Col, Pos(Top, 4)),
ColXtr(1, RowCnd(Interval, Pos(Top, 1), Pos(Bottom, 1), Select(1, 0)), 0),
Replace(2, bottom, StrCnd(null)),
Replace(3, bottom, StrCnd(null)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 9)),
Remove(Row, Pos(Top, 12)),
Remove(Row, Pos(Top, 15))
));


Temp:
LOAD *,if(IsNum(@4),Peek('NewName'),@4) as NewName Resident T1 Order by Rec ;

DROP Table T1;

Final:
LOAD Num(@1,'000') as ID,Date(@2) as Date_Bought,@3 as Product_Bought,subField(NewName,' ',-1) as LastName Resident Temp Where not Match(@2,'Date Bought');

DROP Table Temp;

[tamilarasu]

Another solution could be,

Data:
LOAD Num(@1)           as ID
     ,Date(@2)             as [Date Bought]
     ,@4                   as [Product Bought]
     ,Subfield(@6,' ',2)   as [Last Name]
FROM
Book1.xlsx
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),
Replace(6, top, StrCnd(null))
)) Where Len(Trim(@4))>0 ;

[Anonymous]

Here is the script guys;

LOAD * INLINE [

    John Doe, F2, F3, F4

    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, 1/10/2011, , Case 10

];

here is a screen shot of how it is when doing an Inline load:

I am missing date bought for Case 10 which should be 1/10/2011.

[tamilarasu]

Hi QlikDash,

What is your data source.? How you will load the data in Qlikview in future (via Inline.!!).?

[senpradip007]

Please have a look at the attached. Hope it will help you.

[Digvijay_Singh]

Did you try this?