topic Re: Assigning a value to a column in QlikView

Assigning a value to a column

waleeed_mahmood — Sat, 16 Nov 2024 19:26:20 GMT

Hello,

I have 5 IF....THEN statements which result in a value that i want to assign to an existing column of an existing table. any idea or hint how to achieve that task? I have these statements in FOR Loops. I want to assign "sortNumber" (which is a variable that gets filled with an integer value after successful iteration) to a column "Link To Rule" in previously loaded table. Any help would be appreciated!

Sample Code:

Thank you.

Re: Assigning a value to a column

Kushal_Chawda — Tue, 17 Dec 2019 15:57:20 GMT

Can you please explain what exactly you are trying to do ? what output you need?

Re: Assigning a value to a column

waleeed_mahmood — Tue, 17 Dec 2019 19:01:29 GMT

yes for sure. I have shown a sudo code for my code.

***********************************************************************************

For i=0 to NoOfRows(Table A)

//assign column values to variables

let ProductA= Peek(Table A.ProductNumber);

For j=0 to NoOfRows(Table B)

//assign column values to variables for later use

let ProductB= Peek(Table B.ProductNumber);

if ProductA Like ProductB then

//the image in my initial post shows these if statements

//if all conditions meet

let sortNumber = Peek('Sortnumber',$j,'Table B');

end if

Next j

//Store sortNumber values into a table for later use

Temp:

Load '$(sortNumber)' as rule;

//now i want to fill a column in Table A called "RuleRow" with the values stored in Temp Table as rule

//for example, RuleRow[ith index] = rule[ith index]

Next i

*************************************************************************************************

i hope this makes sense

Re: Assigning a value to a column

waleeed_mahmood — Tue, 17 Dec 2019 19:32:50 GMT

This is what i have tried so far.

Method 1:

after all the loop iterations are done, i try to Concatenate two tables as follows but Qlik gives an error saying it cant find Temp Table:

Concatenate(Temp) Load * Resident Table A;

Drop Table Temp;

Method 2:

I tried to do the following after every successful Jth Iteration:

************************************************

Next j

Temp:

Load *,

'$(sortNumber)' as rule

Resident Table A;

Next i

Drop Table Table A;

*********************************************************

Output of Method 2:

Table A:

Product Number	Product Indicator	RuleRow
abc	ttt
xyz	123
xyz	777
xyz	456

Table B:

SortNumber	Product Number	Product Indicator
30	abc	ttt
50	xyz	123
60	xyz	456
70	kut	323

Output Table: //product number and product indicator has to match for it to assign RuleRow value.

Product Number	Product Indicator	RuleRow
abc	ttt	No Rule
xyz	123	No Rule
xyz	777	No Rule
xyz	456	No Rule
abc	ttt	30
xyz	123	30
xyz	777	30
xyz	456	30
abc	ttt	60
xyz	123	60
xyz	777	60
xyz	456	60
etc	etc	etc

Re: Assigning a value to a column

waleeed_mahmood — Tue, 17 Dec 2019 20:00:13 GMT

DESIRED FUNCTIONALITY EXAMPLE

Table A:

Product Number	Product Indicator	RuleRow
abc	ttt
xyz	123
xyz	777
xyz	456

Table B:

SortNumber	Product Number	Product Indicator
30	abc	ttt
50	xyz	123
60	xyz	456
70	kut	323

Output Table: //product number and product indicator has to match for it to assign RuleRow value.

Product Number	Product Indicator	RuleRow
abc	ttt	30
xyz	123	50
xyz	777	No Rule
xyz	456	60

If my words dont make sense, please use this table as an example.

Re: Assigning a value to a column

Kushal_Chawda — Wed, 18 Dec 2019 09:23:21 GMT

I am not sure why you are using a for loop, but you can achieve it using simple join

Data: //Table A
LOAD
"Product Number",
"Product Indicator"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

left Join(Data) //Table B
LOAD
SortNumber,
"Product Number",
"Product Indicator"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);

Final:
NoConcatenate
Load "Product Number",
"Product Indicator",
if(len(trim(SortNumber))=0,'NoRule',SortNumber) as RuleRow
Resident Data;

Drop Table Data;

Re: Assigning a value to a column

waleeed_mahmood — Mon, 23 Dec 2019 17:08:37 GMT

Hi,

The reason i was using For loops was because my data had wildcards in it. For example, the Indicator Column in Table B has some rows with the following Data:

Table B:

SortNumber	Product Number	Product Indicator
30	abc	*
50	xyz	12*
60	xyz	456
70	kut	*

And with me being new to Qlik i went the LOOP way. I couldnt figure out how to store and use the loop results outside the loop.