Assigning a value to a column

[waleeed_mahmood]

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.

[Kushal_Chawda]

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

[waleeed_mahmood]

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

[waleeed_mahmood]

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

[waleeed_mahmood]

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.

[Kushal_Chawda · Accepted solution]

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;

[waleeed_mahmood]

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.