Reformat table

[#1]

Hello everybody!

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

Is it possible to modify this:

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

To table looks like:

A	B	C
a1	b1	c1
a2	b2	c2
a3	b3	c3

[its_anandrjs]

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.

Regards

Anand

[#3]

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]

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;

[MarcoWedel]

Hi,

one generic solution without hard coded column names could be:

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

[#6]

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)

[MarcoWedel · Accepted solution]

there is always a solution ...

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

[its_anandrjs]

Hi,

Can you share the file format as sample for testing.

Regards

Anand

[#9]

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

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

[#10]

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...