Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Reformat table

Hello everybody!

Need help with script for reformat my table. It's an import from XML file.

Is it possible to modify this:

Header 1Header 2Header 3
Ah1
Bh1
Ch1
a1v2
b1v2
c1v2
a2v3
b2v3
c2v3
a3v4
b3v4
c3v4

To table looks like:

ABC
a1b1c1
a2b2c2
a3b3c3
1 Solution

Accepted Solutions

Re: Reformat table

there is always a solution ...

QlikCommunity_Thread_146305_Pic2.JPG

mapColNames:

Mapping LOAD

  Autonumber(RecNo(),[Header 3]),

  [Header 1]

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='h';

table1:

Generic LOAD

  [Header 3] as %ID,

  Autonumber(RecNo(),[Header 3]) as ColNo,

  [Header 1]

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='v';

RENAME Fields using mapColNames;

tabOutput:

LOAD Distinct

  [Header 3] as %ID

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='v';

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'table1.*') THEN

  LEFT JOIN (tabOutput) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

Using some code from Rob Wunderlichto recombine the tables created by the generic load, although this is not necessary.

See also:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

by Henric Cronström

Use cases for Generic Load | Qlikview Cookbook

by Rob Wunderlich

hope this helps

regards

Marco

View solution in original post

13 Replies

Re: Reformat table

Hi,

You can try this ways for the solution and load three resident table

Source:

LOAD * Inline [

Header 1,    Header 2,    Header 3

A,    h,    1

B,    h,    1

C,    h,    1

a1,    v    2

b1,    v,    2

c1,    v,    2

a2,    v,    3

b2,    v,    3

c2,    v,    3

a3,    v,    4

b3,    v,    4

c3,    v,    4  ];

NewTable:

LOAD [Header 1] as A,

     RowNo() as Rid

Resident Source

Where Left([Header 1],1) = 'a';

Join

LOAD [Header 1] as B,

     RowNo() as Rid

Resident Source

Where Left([Header 1],1) = 'b'; 

Join

LOAD [Header 1] as C,

     RowNo() as Rid

Resident Source

Where Left([Header 1],1) = 'c';

DROP Table Source;

And in place of the Source table you can use your table there.

Data.png

Regards

Anand

Not applicable

Re: Reformat table

Thank you Anand! It's useful but not for my case.

a1, b1 are just examples) there are text strings and digits. We can't filter them with a, b and c. We can group them by Header3 (Row) and use Header2 (h - header, v-value). But i broke my head already to find how!)

alkesh_sharma
Contributor III

Re: Reformat table

OLD: 

LOAD * Inline [ 

Header 1,    Header 2,    Header 3 

A,    h,    1 

B,    h,    1 

C,    h,    1 

a1,    v,   2 

b1,    v,    2 

c1,    v,    2 

a2,    v,    3 

b2,    v,    3 

c2,    v,    3 

a3,    v,    4 

b3,    v,    4 

c3,    v,    4  ];

NEW:

Load [Header 1] AS A,

Rowno() AS Link

Resident OLD

Where Left([Header 1],1) = 'a';

LEFT JOIN

Load [Header 1] AS B,

Rowno() AS Link

Resident OLD

Where Left([Header 1],1) = 'b';

LEFT JOIN

Load [Header 1] AS C,

Rowno() AS Link

Resident OLD

Where Left([Header 1],1) = 'c';

Drop Table OLD;

Re: Reformat table

Hi,

one generic solution without hard coded column names could be:

QlikCommunity_Thread_146305_Pic1.JPG

mapColNames:

Mapping LOAD

  Autonumber(RecNo(),[Header 3]),

  [Header 1]

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='h';

table1:

Generic LOAD

  [Header 3] as %ID,

  Autonumber(RecNo(),[Header 3]) as ColNo,

  [Header 1]

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='v';

RENAME Fields using mapColNames;

hope this helps

regards

Marco

Not applicable

Re: Reformat table

Thanks Marco! Thanks for showing the way! For now it's a more useful solution. But Generic load is not good for me i need 1 table)

Re: Reformat table

there is always a solution ...

QlikCommunity_Thread_146305_Pic2.JPG

mapColNames:

Mapping LOAD

  Autonumber(RecNo(),[Header 3]),

  [Header 1]

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='h';

table1:

Generic LOAD

  [Header 3] as %ID,

  Autonumber(RecNo(),[Header 3]) as ColNo,

  [Header 1]

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='v';

RENAME Fields using mapColNames;

tabOutput:

LOAD Distinct

  [Header 3] as %ID

FROM [http://community.qlik.com/thread/146305](html, codepage is 1252, embedded labels, table is @1)

Where [Header 2]='v';

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'table1.*') THEN

  LEFT JOIN (tabOutput) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

Using some code from Rob Wunderlichto recombine the tables created by the generic load, although this is not necessary.

See also:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

by Henric Cronström

Use cases for Generic Load | Qlikview Cookbook

by Rob Wunderlich

hope this helps

regards

Marco

View solution in original post

Re: Reformat table

Hi,

Can you share the file format as sample for testing.

Regards

Anand

Not applicable

Re: Reformat table

I' ve add source file. You can try it.

Also you can look my QVW. But it's from Personal Edition((

Not applicable

Re: Reformat table

I know, it's all about Qlik here... But isn't it easier to call a small exe that'll open an Excel app and perform a load and then save as .xlsx? Can provide a sample on VB if you like. This way requires MS Excel installed of course...