Change Rows into Column in QliKView

[lawrance]

Hi All,

Could you please help how to resolve below issue in QlikView Script?

Thanks,

Lawrance A

[petter]

LOAD

  Pick(IterNo(),ID,ID1,ID2) AS ID,

  Pick(IterNo(),Name,Name1,Name2) AS Name

FROM

  [SourceData.xlsx]

  (ooxml, embedded labels, table is Sheet1)

WHILE

  IterNo()<=3;

[lawrance]

Thanks for your response.

If we have n number of users then how to achieve this.

Thanks,

Lawrance A

[marcus_sommer]

There are several possibilities. One could be to extend the logic from Petter in that you used variables for the ID- and Name-Lists which are created within a loop.

Another way is using the Qlik file-wizard which has a feature to merge multiple columns into target-columns - after choosing a file with the wizard you need to click on next to get to further windows with various options to transform the data. Unfortunately they aren't (well) documented from Qlik but it's covered within one or two books, for example in: QlikView 11 for Developers.

Personally I never use them because I find usually other solutions more quickly, for example something like this:

t1: crosstable(Categeory, Value, 1)
load rowno() as RowNo, * inline [
ID, Name, ID1, Name1, ID2, Name2
1, A, 2, B, 3, C
4, D, 5, E, 6, F
];

t2: load *, -(even(recno())) as RecNo,
      if(-(even(recno())) = 1, peek('RecID'), rangesum(peek('RecID'), 1)) as RecID resident t1;

t3: noconcatenate load RecNo, RecID, Value as Name resident t2 where RecNo = 1;
      left join(t3)
load RecNo + 1 as RecNo, RecID, Value as ID resident t2 where RecNo = 0;

drop tables t1, t2;

and quite probably there are some more ways to solve such case.

- Marcus

[lawrance]

Thanks for your response. I tried above scenario but facing discrepancies.

Let me try once again and update the status.

Thanks,

Lawrance A

[lawrance]

Hi,

Still I am facing issue to get the required output.

I have coulmns like below

Column Name-> Name 1, Name2.........Name50, ID1,ID2,......ID50,Entry1,.....Entry50, Exit1,.....Exit50.

I tried above scenario again I couldn't achieve this. Is there any other way to achieve this scenario.

Thanks,

Lawrance A

[Anonymous]

Hi, Lawrance Amburose

Try this variation of Petter Skjolden'sanswer:

Table:
LOAD *
FROM
[IDxName.xlsx]
(ooxml, embedded labels, table is Plan1);


LET vFieldNo = NoOfFields('Table');
Set IDlist = 'ID';
Set Namelist = 'Name';


For i = 1 To ($(vFieldNo) / 2) - 1
Let IDlist = IDlist & ',ID' & $(i);
Let Namelist = Namelist & ',Name' & $(i);
Next i


FinalTable:
LOAD
  Pick(IterNo(),$(IDlist)) AS ID,
  Pick(IterNo(),$(Namelist)) AS Name
Resident Table
WHILE IterNo()<=($(vFieldNo) / 2);


Drop Table Table;

[adityaakshaya]

Hi Lawrence,

I think you have posted this question twice. I have answered it there.Change Rows into Column in QliKView

You can try it as below

PS - if you find this solution as correct and helpful, mark it as correct/helpful.

//*************************************************************************************************************

ODBC CONNECT32 TO [Excel;DBQ=C:\Users\Akshaya\Documents\Test\Test.xlsx];

Cols_Temp:

SQLCOLUMNS;

ColumnNo:

Load max(ORDINAL) as MaxOrdinal Resident Cols_Temp;

Let vLoop = Peek('MaxOrdinal',0,ColumnNo)/2-1;

Drop Table Cols_Temp;

Drop Table ColumnNo;

Test:

SQL Select ID,Name from `C:\Users\Akshaya\Documents\Test\Test.xlsx`.`Sheet1$`;

For i = 1 to $(vLoop)

SQL select ID$(i) as ID, Name$(i) as Name from `C:\Users\Akshaya\Documents\Test\Test.xlsx`.`Sheet1$`;

Next i;

[petter]

A lot of people are trying to help you. Be polite enough to put a minimum time to explain what you are looking for so we don't have to waste our time hunting for answers that doesn't solve what you didn't bother to explain...

[lawrance]

Thanks for your response. I should not do like this

Anyway, I will correct myself