topic Re: Change the variable value in the load in QlikView

Change the variable value in the load

Tue, 23 Oct 2012 15:38:35 GMT

Hi everybody,

I need to change the variable value in the load statement,

For example:

if(previous(field)<>field,$(variable)=1);

But it doesn't work.

Thank's

Regards

Re: Change the variable value in the load

Tue, 23 Oct 2012 15:41:53 GMT

Setting a variable needs to be done by SET and you do not need the $. So it should be something along:

If previous(field) <> field

SET variable = 1;

End If

Re: Change the variable value in the load

Tue, 23 Oct 2012 15:45:05 GMT

Thank's for your answer, but I need to change it in a load statement, so in a if(condition,expression) not in ordinary if then end if.

Regards

Re: Change the variable value in the load

Tue, 23 Oct 2012 15:50:02 GMT

So I think it makes sense what you want to do, just what you create is a field where you put that value as your table will have multiple records, you will not create a variable for each record, no?

if(previous(field)<>field,1,0) as NEWFIELD;

Re: Change the variable value in the load

Tue, 23 Oct 2012 15:50:23 GMT

Do you mean with "in a load statement" as a field:

LOAD col1 as a, col2 as b, if(condition, expression) as c FROM ...?

This cannot be done I think. May'be you could give some more information on what you are trying to achieve.

Re: Change the variable value in the load

Tue, 23 Oct 2012 15:57:20 GMT

Hi All

Ducati, this can be done in a load statement exactly as you specifiy:

LOAD Col1, Col2, Col3, IF(Col1=Previous(Col1),'YES','NO') as Col1IsTheSame FROM.........

And Christine is correct, it doesn't make sense creating a variable in a load statement, a variable can only ever hold one value, not an array of values.

Re: Change the variable value in the load

Tue, 23 Oct 2012 15:59:13 GMT

I perhaps wasn't clear, here you are my load statement:


Ruptures:
//if(previous([Code SAP])<>[Code SAP] or previous([Centre de distribution rupture])<>[Centre de distribution rupture]) then $(vCount)=1
LOAD Date, 
  [Code SAP],
     [Centre de distribution rupture], 
     if(num(previous(Date))=num(Date)-1 and previous([Code SAP])=[Code SAP] and previous([Centre de distribution rupture])=[Centre de distribution rupture],$(vCount)+1,$(vCount)) as nbRupture,
     if(previous([Code SAP])<>[Code SAP] or previous([Centre de distribution rupture])<>[Centre de distribution rupture],set vCount=1),
  numLigne,
     [Code GMID], 
     [Stock à date], 
     [SLA MIN], 
     [SLA MAX], 
     [Code usine], 
     [Coordinateur logistique rupture], 
     [Catégorie produit], 
     Responsabilité, 
     [Semaine prévisionnelle de fin de rupture], 
     [Codes Commentaires], 
     [Commentaire libre],
  [Commentaires automatiques], 
     Cause
Resident Ruptures_temp
order by [Code SAP],[Centre de distribution rupture],Date;

I would like to compare previous values, if it change, the variable has to take 1 as value.

Thank's

Regards

Re: Change the variable value in the load

Tue, 23 Oct 2012 16:07:05 GMT

I want that the field take the variable value, and if the previous product isn't the product of my current record, I want to reset the variable value to 1

Thak's

Regards

Re: Change the variable value in the load

Tue, 23 Oct 2012 16:11:41 GMT

That is exactly our point though, you really dont want to create a variable, you want to create a column.

This column becomes part of your table and has a value of 1 if the current row in the table has the same value as the previous row for Code SAP.

You cannot do this with a variable because it holds only one value, therefore the value of vCount will either be zero or 1 at the end of processing, so my previous comment and load script statement still stands.

Now.....

If you then want to have to total number of rows in your table that have the same previous value, that is different, you could then add the following:

AggrTable:

LOAD Sum(YourColumnNameOfCount) AS SumOfCount RESIDENT Ruptures;

LET vCount = PEEK('AggrTable','SumOfCount',0);

DROP TABLE AggrTable;

This will then give you a variable then holds the total number of rows in your table that have the same prevoius value.

Hope it helps.

Re: Change the variable value in the load

Tue, 23 Oct 2012 16:30:27 GMT

Actually, now I think I understand you, I think what you want is that when the value of Code SAP is new, the value of the column should be 1, and for each row that it remains the same, the value of the column should increment, then when the value changes again, it should revert back to 1.

If my understanding is correct then you can do the following:

Ruptures:

LOAD *, IF(nbRupture=1,Peek('nbRuptureNew')+1,1) as nbRuptureNew;

LOAD Date,

[Code SAP],

[Centre de distribution rupture],

if(num(previous(Date))=num(Date)-1 and previous([Code SAP])=[Code SAP] and previous([Centre de distribution rupture])=[Centre de distribution rupture],1,0) as nbRupture,

etc

Re: Change the variable value in the load

Wed, 24 Oct 2012 07:25:12 GMT

It works, Thank you Nigel

Re: Change the variable value in the load

Wed, 24 Oct 2012 07:33:18 GMT

My pleasure