topic Re: Loop through column in App Development

Loop through column

MP1 — Tue, 09 Jul 2019 15:31:34 GMT

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.

Re: Loop through column

Rodrigo_Reis — Tue, 09 Jul 2019 20:39:44 GMT

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

Re: Loop through column

MP1 — Wed, 10 Jul 2019 08:36:40 GMT

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

Re: Loop through column

anushree1 — Wed, 10 Jul 2019 09:39:56 GMT

Please check if the attached file works for you

Re: Loop through column

MP1 — Wed, 10 Jul 2019 09:52:10 GMT

I can't download the file

Re: Loop through column

anushree1 — Wed, 10 Jul 2019 09:58:31 GMT

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;

Re: Loop through column

MP1 — Wed, 10 Jul 2019 15:07:22 GMT

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.

Re: Loop through column

anushree1 — Thu, 11 Jul 2019 09:31:04 GMT

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

Re: Loop through column

MP1 — Thu, 11 Jul 2019 14:27:24 GMT

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?

Re: Loop through column

anushree1 — Fri, 12 Jul 2019 06:33:56 GMT

Load Distinct

Field1,

Field2,...

From /Resident .....;

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

Re: Loop through column

MP1 — Fri, 12 Jul 2019 11:25:13 GMT

In Table 1 , Richard has 1 as value for A1 and A4. and because of that it's reflecting twice in tab2 table. My requirement is in tab 2, I only need one row for each user ( any random out of two ) .

Table 1:

Name	A1	A2	A3	A4
Sam	1
Linda			1
Richard	1			1
Sandy		1
Ben				1
Tom				1

lookup table :-

Code	Area
A1	ABC
A2	XYZ
A3	ABC
A4	EFG

Tab 2:

Name	Code	Value
Sam	ABC	1
Linda	ABC	1
Richard	ABC	1
Richard	EFG	1
Sandy	XYZ	1
Ben	EFG	1
Tom	EFG	1