Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
qlikdash
Valued Contributor

How to allign rows and columns

John Doe
IDDate BoughtProduct Bought
0011/1/2011Case 1
0021/2/2011Case 2
0031/3/2011Case 3
Mary Wu
IDDate BoughtProduct Bought
0041/4/2011Case 4
0051/5/2011Case 5
Linda Jones
IDDate BoughtProduct Bought
0061/6/2011Case 6
0071/7/2011Case 7
Joe White
IDDate BoughtProduct Bought
0081/8/2011Case 8
0091/9/2011Case 9
Jim Kenny
IDDate BoughtProduct Bought
01012/31/2015Case 10

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

IDDate BoughtLast NameProduct Bought
0011/1/2011DoeCase 1
0021/2/2011DoeCase 2
0031/3/2011DoeCase 3
0041/4/2011WuCase 4
0051/5/2011WuCase 5
0061/6/2011JonesCase 6
0071/7/2011JonesCase 7
0081/8/2011WhiteCase 8
0091/9/2011WhiteCase 9
01012/31/2015KennyCase 10

Thanks much appreciated

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: How to allign rows and columns

At least this works for your sample

Capture.JPG

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;

18 Replies

Re: How to allign rows and columns

Hi,

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

MVP & Luminary
MVP & Luminary

Re: How to allign rows and columns

HI,

Can you attach some sample data?

Regards,

Jagan.

Digvijay_Singh
Honored Contributor III

Re: How to allign rows and columns

At least this works for your sample

Capture.JPG

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;

Re: How to allign rows and columns

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;

Capture.JPG

Re: How to allign rows and columns

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 ;

Capture.PNG

qlikdash
Valued Contributor

Re: How to allign rows and columns

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.

align fields.PNG

Re: How to allign rows and columns

Hi QlikDash,

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

senpradip007
Valued Contributor III

Re: How to allign rows and columns

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

Digvijay_Singh
Honored Contributor III

Re: How to allign rows and columns

Did you try this?