topic Re: Change Rows into Column in QliKView in QlikView

Change Rows into Column in QliKView

lawrance — Tue, 05 Jun 2018 17:38:24 GMT

Hi All,

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

Thanks,

Lawrance A

Re: Change Rows into Column in QliKView

petter — Tue, 05 Jun 2018 22:37:16 GMT

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;

Re: Change Rows into Column in QliKView

lawrance — Wed, 06 Jun 2018 07:32:32 GMT

Thanks for your response.

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

Thanks,

Lawrance A

Re: Change Rows into Column in QliKView

marcus_sommer — Wed, 06 Jun 2018 12:10:12 GMT

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

Re: Change Rows into Column in QliKView

lawrance — Wed, 06 Jun 2018 15:45:05 GMT

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

Let me try once again and update the status.

Thanks,

Lawrance A

Re: Change Rows into Column in QliKView

lawrance — Thu, 07 Jun 2018 18:29:00 GMT

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

Re: Change Rows into Column in QliKView

Anonymous — Thu, 07 Jun 2018 19:50:09 GMT

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;

Re: Change Rows into Column in QliKView

adityaakshaya — Thu, 07 Jun 2018 21:05:14 GMT

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;

Re: Change Rows into Column in QliKView

petter — Thu, 07 Jun 2018 21:55:34 GMT

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

Re: Change Rows into Column in QliKView

lawrance — Fri, 08 Jun 2018 03:20:05 GMT

Thanks for your response. I should not do like this

Anyway, I will correct myself

Re: Change Rows into Column in QliKView

lawrance — Sat, 09 Jun 2018 02:07:34 GMT

Thanks for all your response.

I got expected result by using my below script.

NoConcatenate

Temp:

LOAD Distinct ID,

Status

FROM

[tab2.qvd]

(qvd) where len(ID)>0;

For i=1 to 188

NoConcatenate

Concatenate(Temp)

LOAD Distinct ID$(i) as ID,

Status$(i)

FROM

(qvd) where len(ID)>0;

NEXT i

Thanks,

Lawrance A

Re: Change Rows into Column in QliKView

petter — Sat, 09 Jun 2018 04:48:31 GMT

This might interest you as a much more optimized solution which is also totally flexible on how many columns the source QVD contains:

TEMP:
LOAD   
  // Get the ID from previous row if on an even numbered row.
  If( Mod(RowNo(),2) = 0 , Peek('ID'), @1 ) AS ID,  
  @1 AS Status
FROM
  tab2.qvd (qvd,filters(Transpose());  


DATA:
NOCONCATENATE LOAD
  ID,
  Status
RESIDENT
  TEMP
WHERE
  // remove every odd numbered row since they are not necessary anymore
  Mod(RecNo(),2) = 0 AND Len(ID)>0;   
  
DROP TABLE TEMP;

Re: Change Rows into Column in QliKView

petter — Fri, 29 Jun 2018 14:26:30 GMT

If the question has been answered please mark the right response as CORRECT and if you feel like it give all the persons that were helpful a HELPFUL tag on their responses too.

Thanks