Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
waleeed_mahmood
Creator
Creator

Assigning a value to a column

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:

InkedCapture_LI.jpg

 

Thank you.

Labels (7)
1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

6 Replies
Kushal_Chawda

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

waleeed_mahmood
Creator
Creator
Author

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
Creator
Creator
Author

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 NumberProduct IndicatorRuleRow
abcttt 
xyz123 
xyz777 
xyz456 

 

Table B:

SortNumberProduct NumberProduct Indicator
30abcttt
50xyz123
60xyz456
70kut323

 

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

Product NumberProduct IndicatorRuleRow
abctttNo Rule
xyz123No Rule
xyz777No Rule
xyz456No Rule
abcttt30
xyz12330
xyz77730
xyz45630
abcttt60
xyz12360
xyz77760
xyz45660
etcetcetc

 

 

waleeed_mahmood
Creator
Creator
Author

DESIRED FUNCTIONALITY EXAMPLE

Table A:

Product NumberProduct IndicatorRuleRow
abcttt 
xyz123 
xyz777 
xyz456 

 

Table B:

SortNumberProduct NumberProduct Indicator
30abcttt
50xyz123
60xyz456
70kut323

 

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

Product NumberProduct IndicatorRuleRow
abcttt30
xyz12350
xyz777 No Rule
xyz456 60

 

 

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

 

Kushal_Chawda

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
Creator
Creator
Author

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:

SortNumberProduct NumberProduct Indicator
30abc*
50xyz12*
60xyz456
70kut*

 

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.