topic Re: Urgent Help plz - For loop in QlikView

Urgent Help plz - For loop

lavanya14 — Thu, 03 Apr 2014 07:53:54 GMT

Hi All,

I have a table called equipment and i need one more date column through loop lets say

ColumnA ColumnB ColumnC NewDateColumn

1 a 1234 3-Apr-2014

2 b 5678 3-Apr-2014

3 c 9876 3-Apr-2014

1 a 1234 4- Apr-2014

2 b 5678 4- Apr-2014

3 c 9876 4- Apr-2014

1 a 1234 5- Apr-2014

2 b 5678 5- Apr-2014

3 c 9876 5- Apr-2014

Basically I should have 3 days of date generated for the entire data available in my Table. Lets say for entire data available in the table it should have 3-Apr-2014 and similarly the available data in table should have 4-Apr-2014.Same follows for 5-Apr-2014.

Any help would be appreciated.

Thanks in advance.

Re: Urgent Help plz - For loop

crusader_ — Thu, 03 Apr 2014 08:01:34 GMT

Dear Lavanya,

Seems that you need just create a pivot table with Date as a first Dimension, but...

Could you provide result tables?

Thanks

Andrei

Re: Urgent Help plz - For loop

its_anandrjs — Thu, 03 Apr 2014 08:04:00 GMT

Can you share any sample data for this and let me know how you create a NewDateColumn column in the table

Re: Urgent Help plz - For loop

flipside — Thu, 03 Apr 2014 08:04:27 GMT

In script,

Data:

Load * inline [
ColumnA,     ColumnB,     ColumnC
1,                 a,                1234
2,                 b,                5678
3,                 c,                9876
];

left join (Data)
load * inline [
NewDate
3-April-2014
4-April-2014
5-April-2014
];

flipside

Re: Urgent Help plz - For loop

tresB — Thu, 03 Apr 2014 08:17:40 GMT

May be like attached sample:

Let count=3;
Let StartDate=Num(Date#('3-4-2014', 'DD-MM-YYYY'));

For i=0 to $(count)-1

Load
*,
Date($(StartDate)+$(i)) as Date
;
Load * Inline [
ColumnA, ColumnB, ColumnC

1, a, 1234

2, b, 5678

3, c, 9876

];
Next i ;

Re: Urgent Help plz - For loop

lavanya14 — Thu, 03 Apr 2014 08:19:18 GMT

Hi All,

Thanks for your quick response.

Let vMin_Equ = Today();
Let vMax_Equ = Date(Today()+5);

For i =vMin_Equ to vMax_Equ

vDate = Date(vMin_Equ+$(i));

Equipment:
Load *,
1 as dummy,
('$(vDate)') as UI_Date
Resident EQUIPMENT;

DROP Table EQUIPMENT;

When I run this code i am not able to see the UI_Date Field having the values 3-Apr,4-Apr,5-Apr. Instaed i see the below data in UI_Date.

UI_Date 06/07/2128 07/07/2128 08/07/2128 09/07/2128 10/07/2128 11/07/2128

But if i look at variable overview i see the values of

vMin_Equ = 3-Apr-2014

vMax_Equ = 8-Apr-2014.

Kindly suggestion what is causing the problem.

Thanks in advance.

Re: Urgent Help plz - For loop

lavanya14 — Thu, 03 Apr 2014 08:22:29 GMT

Hi,

Could you please tell if i dnt want to use the Inline table and make the script to pick the dates automatically from today() to today()+5 days in the inline table.

Thanks in advance.

Re: Urgent Help plz - For loop

tresB — Thu, 03 Apr 2014 08:23:13 GMT

Try:

Date('$(vDate)' , 'DD-MMM-YYYY') as UI_Date

Re: Urgent Help plz - For loop

lavanya14 — Thu, 03 Apr 2014 08:26:35 GMT

Tried but No Luck

Re: Urgent Help plz - For loop

lavanya14 — Thu, 03 Apr 2014 08:28:04 GMT

Not sure what could be the reason of seeing different values in Variable Overview and in the Newly created field in backend..

In Variable Overview I am able to see the corect value for Vmin_Equ = 03-Apr and Vamx=04-Apr.

Re: Urgent Help plz - For loop

crusader_ — Thu, 03 Apr 2014 08:29:08 GMT

Lavanya,

Try in FOR statement convert your variables into NUM

Andrei

Re: Urgent Help plz - For loop

Thu, 03 Apr 2014 08:29:47 GMT

Hi,

Try this

Load * Inline [
ColumnA, ColumnB, ColumnC
1, a, 1234
2 , b, 5678
3, c, 9876
1, a, 1234
2, b, 5678
3, c, 9876
1, a, 1234
2, b, 5678
3, c, 9876
];
Let vColumnA = FieldValueCount('ColumnA');
For A = 1 to $(vColumnA)
Let vNewDateColumn = $(A)+2&'-Apr-2014';
For i = 1 to $(vColumnA)
LoopTest1:
Load

*,
'$(vNewDateColumn)' as NewDateColumn
Resident LoopTest;
Next;
Next;
Drop Table LoopTest;

Re: Urgent Help plz - For loop

tresB — Thu, 03 Apr 2014 08:30:06 GMT

Ahh, I missed the actual issue, it is not about format but the loop variable.

Try:

For i = 0 to (vMax_Equ -vMin_Equ -1 )

Or,

For i = 0 to ($(vMax_Equ) - $(vMin_Equ) -1)

Re: Urgent Help plz - For loop

flipside — Thu, 03 Apr 2014 08:36:03 GMT

Of course, just replace the inline table with ...

load date(today()+rowno()-1) as NewDate autogenerate 5;

flipside

Re: Urgent Help plz - For loop

lavanya14 — Thu, 03 Apr 2014 08:37:48 GMT

Hi ,

Thats a great help !! Thank you !!