Loop through column

[MP1]

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.

[Rodrigo_Reis]

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

[MP1]

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]

Please check if the attached file works for you

[MP1]

I can't download the file

[anushree1 · Accepted solution]

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]

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]

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

[MP1]

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]

Load Distinct 

Field1,

Field2,...

From /Resident .....;

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