Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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 |
Please check if the attached file works for you
I can't download the file
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;
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.
Its probably because we are using preceeding load , try using resident load and they apply cross table, it should work then
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?
Load Distinct
Field1,
Field2,...
From /Resident .....;
Should work , if it is not I would suggest you to share a sample to recheck