Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

MP1
New Contributor

Loop through column

Hi, 

I want to write logic for looping through columns in table for the particular row.

Can we use numbers to recognise the columns or is there any other way to iterate through columns?

 

 

Thanks In Advance.

 

1 Solution

Accepted Solutions
anushree1
Valued Contributor

Re: Loop through column

Use the below Script:

Code_Map:
Mapping LOAD
Code,
Area
FROM [lib://Web_Conn_Map_Col]
(html, utf8, embedded labels, table is @2);
Tab:
CrossTable(Code,Val)
LOAD
Name,
A1,
A2,
A3,
A4
FROM [lib://Web_Conn_Map_Col]
(html, utf8, embedded labels, table is @1)
;
NoConcatenate
Tab2:
Load * ,
ApplyMap('Code_Map',Code,'NA') as NewCol
Resident Tab
where Val>0;
drop table Tab;

drop field Val;

 

 

10 Replies
Rodrigo_Reis
New Contributor II

Re: Loop through column

Can you upload a real example?
Maybe there are another ways to do what you need
MP1
New Contributor

Re: Loop through column

 

Hi, 

Thanks for replying.

I have dataset that is in below format. There are around 100 columns in the original dataset and more are being added.  The values for the data in the original dataset is 1 or blank. I need to use lookup table to map the values of the column name to code column and get respective area code . And add an extra column in the Table 1 with area code as value. 

I've done the same thing in excel before , where I used the lookup & vlookup functions for the new column and it gave me the desired output. but I'm not sure if there are is any equivalent function in Qliksense

 

Table 1:

Name

A1

A2

A3

A4

Column Required to be added

Sam

1

 

 

 

ABC

Linda

 

 

1

 

ABC

Richard

1

 

 

 

ABC

Sandy

 

1

 

 

XYZ

Ben

 

 

 

1

EFG

Tom

 

 

 

1

EFG

 

Look Up table :

Code

Area

A1

ABC

A2

XYZ

A3

ABC

A4

EFG

 

anushree1
Valued Contributor

Re: Loop through column

Please check if the attached file works for you

MP1
New Contributor

Re: Loop through column

I can't download the file

anushree1
Valued Contributor

Re: Loop through column

Use the below Script:

Code_Map:
Mapping LOAD
Code,
Area
FROM [lib://Web_Conn_Map_Col]
(html, utf8, embedded labels, table is @2);
Tab:
CrossTable(Code,Val)
LOAD
Name,
A1,
A2,
A3,
A4
FROM [lib://Web_Conn_Map_Col]
(html, utf8, embedded labels, table is @1)
;
NoConcatenate
Tab2:
Load * ,
ApplyMap('Code_Map',Code,'NA') as NewCol
Resident Tab
where Val>0;
drop table Tab;

drop field Val;

 

 

MP1
New Contributor

Re: Loop through column

Thanks Anushree1 for the solution It works as I wanted.

One more question. 

 My database is in Microsoft SQL server. I've created a connection for that as well. I've loaded my data using same connection. 

In cross table for from , when I use the same path  I used to initially load data it gives me error message. 

I had to try your solution using a excel file as source which worked fine.  

anushree1
Valued Contributor

Re: Loop through column

Its probably because we are using preceeding load , try using resident load and they apply cross table, it should work then

MP1
New Contributor

Re: Loop through column

In the Tab2 after the transformation , I've various Names which are duplicate with different area code. 

I used distinct in the  tab table but didn't work. If I want to apply distinct on table tab2 which syntax should I use?

 

anushree1
Valued Contributor

Re: Loop through column

Load Distinct 

Field1,

Field2,...

From /Resident .....;

Should work , if it is not I would suggest you to share a sample to recheck