topic Re: How to allign rows and columns in QlikView

How to allign rows and columns

Anonymous — Thu, 17 Mar 2016 01:57:46 GMT

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

Re: How to allign rows and columns

tamilarasu — Thu, 17 Mar 2016 02:05:02 GMT

Hi,

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

Re: How to allign rows and columns

jagan — Thu, 17 Mar 2016 02:48:00 GMT

HI,

Can you attach some sample data?

Regards,

Jagan.

Re: How to allign rows and columns

Digvijay_Singh — Thu, 17 Mar 2016 03:37:52 GMT

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;

Re: How to allign rows and columns

settu_periasamy — Thu, 17 Mar 2016 03:58:22 GMT

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;

Re: How to allign rows and columns

tamilarasu — Thu, 17 Mar 2016 04:01:02 GMT

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 ;

Re: How to allign rows and columns

Anonymous — Thu, 17 Mar 2016 04:22:34 GMT

Here is the script guys;

LOAD * INLINE [

John Doe, F2, F3, F4