topic Re: Reformat table in QlikView

Reformat table

Fri, 19 Dec 2014 07:51:58 GMT

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

Re: Reformat table

its_anandrjs — Fri, 19 Dec 2014 08:34:34 GMT

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

Re: Reformat table

Fri, 19 Dec 2014 08:51:19 GMT

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!)

Re: Reformat table

alkesh_sharma — Fri, 19 Dec 2014 08:54:01 GMT

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

MarcoWedel — Fri, 19 Dec 2014 10:29:02 GMT

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

Re: Reformat table

Fri, 19 Dec 2014 12:28:03 GMT

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

MarcoWedel — Fri, 19 Dec 2014 12:53:31 GMT

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.

Re: Reformat table

its_anandrjs — Fri, 19 Dec 2014 13:16:40 GMT

Hi,

Can you share the file format as sample for testing.

Regards

Anand

Re: Reformat table

Fri, 19 Dec 2014 15:10:15 GMT

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

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

Re: Reformat table

Fri, 19 Dec 2014 21:34:36 GMT

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

Re: Reformat table

Sun, 21 Dec 2014 18:20:27 GMT

Sure it is! Waiting for VB sample) But previous answer from Marco is also helpful!

Re: Reformat table

Sun, 21 Dec 2014 18:23:31 GMT

I think this is really one of the best solution)

Re: Reformat table

MarcoWedel — Sun, 21 Dec 2014 22:56:12 GMT

Glad you like it.

regards

Marco

Re: Reformat table

Sun, 21 Dec 2014 23:41:15 GMT

Sure:

Module Module1
    Sub Main(ByVal Args() As String)
        Dim alr As Boolean, src As String, dst As String
        If Args.Length <> 3 Then
            Console.WriteLine("Usage: xml2xlsx.exe Visibility Source Destination")
            GoTo EOS
        End If
        alr = Args(0)
        src = Args(1)
        dst = Args(2)
    Dim appe As Object
        appe = CreateObject("Excel.Application")
        appe.Visible = CBool(alr)
        appe.displayalerts = False
        appe.Workbooks.Open(src)
        appe.Workbooks(1).SaveAs(dst, 51)
        appe.Workbooks(1).Close()
        appe.Quit()
EOS:
    End Sub
End Module

And the ready command line app attached...

Parameters:

Visibility - Tells whether the excel will be visible (1) or not (0) while performing Open/SaveAs

Source - Full path to xml file

Destination - Full path to result xlsx file

like that: xml2xlsx.exe 1 e:\test\AV.xml e:\test\av.xlsx