How to display the value of a some variable with the help of load statement?

[#1]

Hello Everyone,

I have calculated certain fields for 1 record I got from Database.

The calculation are quite complicated , and I have to create some varibales for that.

I have created 3 variables var1, var2 , var3 for these.

Now I want to store these three variables in a row correspondin to the row..

Then I have to do the same calculation for more rows from the table .

So what I am doing right now is calculating those values and storing the values with the help of

Table1:

SQL select '$(var1)' ,$(var2)' ,$(var3)'  from dual;

Then I am looping the same logic for getting these values for different rows in table.

I am getting the desired result by this process.But the problem is that the query is taking much time.

As it has to unnecessary create database link,execute query over there , and then fetch the result back to QlikView.

Can't we have some dummy table in QlikView which will store the result here itself.

As I just need to store the values row wise corresponding to each record.

When calculation is done for each file , I will just make a .qvd for the table.

Kindly help.

[#2]

Please reply!!!

I need the solution desperately

Please let me now if I need to through more light on my problem.

[kaushiknsolanki]

HI,

   Can you explain with example what you want to achieve.

Regards,

Kaushik Solanki

[#4]

Thanks for the reply I was desperatley waiting for that.

My condition is like that I have some work_id , start_date on which order is placed, and offset value which tells us that on which day the order is going to be delivered. All thse comes from one table called work_details in database.

Now for calculating the value I have to get all the dates between the start_date and offset_value.

Then I check for the dates on a different table to make sure that the day is not a holiday.

Then depending on the logic I calculate the value of the required date corresponding to a work_id.

Now as I have to do the same comparison for all the records present in work_details, I simply made the logic I ran it for all the reocrds.

For this I used peek and for.

To get first record peek('start_date' ,$(i) );

where I is a counter defined in for loop which value covers all the records in work_details table.

Now whenever desired delivery date is calculated for a work_id n I need to sotre it in a table ,and the advance my counter to fetch the next record.

I am presently implementing that my quering from DUAL table in oracle.

Table1:

SQL select '$(var1)' ,$(var2)'   from dual;

here var1 conains the work_id for Ith record, and var2 contains the delivery date corresponding to Ith work_id.

As soon as I get a value , I increment the counter to fetch the next column.

This logic is working fine.

The only problem is that it's talking much time as  for each record to be inserted in Table1 it has to create database link, then execute query there , and then bring the result from database to QlikView.

I was thinking that if I am able to create a dummy table in QlikView itself , and store result row wise there , then the running time of my query will drastically reduce.

So , can there be any substitute for

Table1:

SQL select '$(var1)' ,$(var2)'   from dual;

What I was thinking was something like

Table1 :

Load '$(var1)' ,$(var2)'   from Dummy_QlikView_table;

I hope I have made my point clear.

Please revert if you want some more explaination.

Thanks in adavnce !!!!!

[kaushiknsolanki]

Hi,

   I got what you said, but i want to know actually what your data look like, so can you please explain with some sample data.

  Or can you upload the script you are using.

Regards,

Kaushik Solanki

[#6]

Hi,

My sample data will be something like this.

Suppose in table called work_details we have two rows.

Work_id       Start_date     Date_offset

1               18/07/2011      6

2                14/07/2011     5

Now My production plant doesn't work on saturday and sunday.

21/07/2011 is a national holiday, and hence present in holiday master.

Now we need to calculate 6th working day for work_id 1 and 5th working day for work_id 2.

What I do now is check whether the start date is a weekend or not.If it's not a weekend , then I check whether it's a national holiday or not.By this I get the desired working day.

For this I have created variable say find_date And always increase it's value by one.I ahve made a counter to check the working day got.

For record 1 i.e work_id = 1 , we  get the work day as 27/07/2011.(21 is national holiday and 23 ,24 are weekends)

Now I store this record in a table.

For this I have to take help of SQL Dummy table (DUAL) .This is creating unnecessary burden .

For record 2 the logic is same.So I have made a loop to navigate the records on .

For record 2 i.e work_id = 2 , we  get the work day as 22/07/2011(16,17 are weekends and 21 is national holiday).

Then I write those records on same table.

I have also attached the script I am using.

But the script is havin more conditions applied.

I have described things simply here.

As you will see that I have to make unnecessary reference to Dual for getting my calculated field's value to I_FILE table.This is taking significant time as it has to access database unnecessary.

If I can just fire that query on any temporary QlikView table , then it would save considerable time.

Like somthing like

  I_FILE:

    load '$(WO_NBR)' WO_NBR,'$(WO_LOT)' WO_LOT,'$(L_FIND_DATE)' as CYCLEDATE from Temp_qlikView_table;

Thanks in advance !!!

-------------------------------------------------------------------------------------------------------------------------------------------------------------

TEMP:
SQL SELECT COUNT(*) AS TOT_COUNT FROM wo_mstr;
LET TOT_RECORD= PEEK('TOT_COUNT',0,'TEMP');
DROP TABLE TEMP;
HOLIDAY:
LOAD HD_SITE,HD_DATE;
SQL SELECT UPPER(HD_SITE) HD_SITE,HD_DATE FROM HD_MSTR;

SHOP_CAL:
LOAD SHOP_SITE,SHOP_WDAYS##1 ,SHOP_WDAYS##2 ,SHOP_WDAYS##3,SHOP_WDAYS##4 ,SHOP_WDAYS##5 ,SHOP_WDAYS##6 ,SHOP_WDAYS##7;
SQL SELECT UPPER(SHOP_SITE) SHOP_SITE,SHOP_WDAYS##1 ,SHOP_WDAYS##2 ,SHOP_WDAYS##3,SHOP_WDAYS##4 ,SHOP_WDAYS##5 ,SHOP_WDAYS##6 ,SHOP_WDAYS##7
FROM SHOP_CAL
WHERE SHOP_WKCTR=' '
AND SHOP_MCH=' ' ;

table1:
load
WO_NBR,
WO_LOT,
WO_REL_DATE,
PT_MFG_LEAD,
WO_SITE,
WO_STATUS,
RCTDATE;
SELECT WO_NBR,WO_LOT,WO_REL_DATE,WO_DUE_DATE,PT_MFG_LEAD,WO_SITE,WO_STATUS,RCTDATE FROM wo_mstr
FOR I= 0 TO $(TOT_RECORD)-1
LET WO_NBR= PEEK('WO_NBR',$(I),'table1');
LET WO_LOT= PEEK('WO_LOT',$(I),'table1');
LET date_offset = PEEK('PT_MFG_LEAD',$(I),'table1');
LET site = PEEK('WO_SITE',$(I),'table1');
LET start_date =  Date(PEEK('WO_REL_DATE',$(I),'table1'),'MM/DD/YYYY');
LET WO_STATUS = PEEK('WO_STATUS',$(I),'table1');
LET RCTDATE =  Date(PEEK('RCTDATE',$(I),'table1'),'MM/DD/YYYY');
let L_INTERVAL=0;
let L_FORWARD =0;
let L_FIND_DATE =0;
let curr_day =0;
let HLDY_COUNT =0;
let L_HOLIDAY=0 ;
LET DAY1=-1;
LET DAY2=-1;
LET DAY3=-1;
LET DAY4=-1;
LET DAY5=-1;
LET DAY6=-1;
LET DAY7=-1;
let curr_day = 'Mon';

if date_offset >= 0 then
  L_INTERVAL = $(date_offset);
  L_FORWARD = 1;
else
       L_INTERVAL = -1*$(date_offset);
       L_FORWARD = -1;
end if


CYCLEDATE_TABLE :
LOAD
  SHOP_WDAYS##1 ,SHOP_WDAYS##2 ,SHOP_WDAYS##3,SHOP_WDAYS##4 ,SHOP_WDAYS##5 ,SHOP_WDAYS##6 ,SHOP_WDAYS##7
  Resident [SHOP_CAL]
  where  [SHOP_SITE]='$(site)';

if PEEK('SHOP_WDAYS##1',0,'CYCLEDATE_TABLE') = 1 or PEEK('SHOP_WDAYS##1',0,'CYCLEDATE_TABLE') = 0 then
  LET DAY1 = PEEK('SHOP_WDAYS##1',0,'CYCLEDATE_TABLE');
  LET DAY2 = PEEK('SHOP_WDAYS##2',0,'CYCLEDATE_TABLE');
  LET DAY3 = PEEK('SHOP_WDAYS##3',0,'CYCLEDATE_TABLE');
  LET DAY4 = PEEK('SHOP_WDAYS##4',0,'CYCLEDATE_TABLE');
  LET DAY5 = PEEK('SHOP_WDAYS##5',0,'CYCLEDATE_TABLE');
  LET DAY6 = PEEK('SHOP_WDAYS##6',0,'CYCLEDATE_TABLE');
  LET DAY7 = PEEK('SHOP_WDAYS##7',0,'CYCLEDATE_TABLE');
  let L_FIND_DATE = Date(start_date ,'MM/DD/YYYY');
    do while L_INTERVAL > 0 and (curr_day = 'Mon' or  curr_day = 'Tue' or  curr_day = 'Wed' or  curr_day = 'Thu' or  curr_day = 'Fri' or  curr_day = 'Sat' or  curr_day = 'Sun')
   LET L_FIND_DATE = Date(L_FIND_DATE + L_FORWARD,'MM/DD/YYYY');
   curr_day=weekday(L_FIND_DATE);
   if curr_day = 'Mon' or  curr_day = 'Tue' or  curr_day = 'Wed' or  curr_day = 'Thu' or  curr_day = 'Fri' or  curr_day = 'Sat' or  curr_day = 'Sun' then
    temp_hd:
    LOAD
     1 as HLDY
     Resident [HOLIDAY]
     where  [HD_SITE]='$(site)' and date([HD_DATE],'MM/DD/YYYY')='$(L_FIND_DATE)';
     let HLDY_COUNT = peek('HLDY',0,'temp_hd');
     drop table temp_hd;
     if HLDY_COUNT <> 1 and curr_day = 'Sun' and DAY1 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Mon' and DAY2 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Tue' and DAY3 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Wed' and DAY4 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> '$(site)' and curr_day = 'Thu' and DAY5 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) -  1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Fri' and DAY6 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Sat' and DAY7 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     END IF
    end if
    loop
END IF
DROP TABLE CYCLEDATE_TABLE;
  I_FILE:
    select '$(WO_NBR)' WO_NBR,'$(WO_LOT)' WO_LOT,'$(L_FIND_DATE)' as CYCLEDATE from dual;
  NEXT
  DROP TABLE SHOP_CAL;
  drop table table1;
  DROP TABLE HOLIDAY;

[kaushiknsolanki]

Hi,

   Let me tell you what we do when we want some variable values to be in a table.

   load '$(ABC)' as ABC

    Autogenerate 1;

   This will create a single record, where in ABC field you will get the value of Variable ABC.

   Now in your case, you are using the below statement in loop.

    I_FILE:

    select '$(WO_NBR)' WO_NBR,'$(WO_LOT)' WO_LOT,'$(L_FIND_DATE)' as CYCLEDATE from dual;

    So instead of this you can try.

    I_FILEl:

     load '$(WO_NBR)' as  WO_NBR,'$(WO_LOT)'  as WO_LOT,'$(L_FIND_DATE)' as CYCLEDATE

    Autogenerate 1;

Regards,

Kaushik Solanki

[#8]

Hi Kaushik,

Thanks for the reply.

It's just working for one record

From second record onwards it's giving Misplaced from.

----------------

Syntax error, missing/misplaced FROM:

I_FILE:

    load 'NS198303' WO_NBR,'NS198303' WO_LOT,'07/12/2011' as CYCLEDATE

    autogenerate 1

I_FILE:

    load 'NS198303' WO_NBR,'NS198303' WO_LOT,'07/12/2011' as CYCLEDATE

    autogenerate 1

----------------------

I think autogenerate 1 generates only one row.

Here I am not dropping the records in I_FILE .

Instead I am creating those many rows as there are in wo_mstr table.

[kaushiknsolanki]

Hi

   Make sure that it is in between the for loop.

Regards,

Kaushik Solanki 

[#10]

Hi,

I have put it in the for loop itself.

This is what I am trying right now.

------------

TEMP:

SQL SELECT COUNT(*) AS TOT_COUNT FROM wo_mstr;

LET TOT_RECORD= PEEK('TOT_COUNT',0,'TEMP');

DROP TABLE TEMP;

HOLIDAY:

LOAD HD_SITE,HD_DATE;

SQL SELECT UPPER(HD_SITE) HD_SITE,HD_DATE FROM HD_MSTR;

SHOP_CAL:
LOAD SHOP_SITE,SHOP_WDAYS##1 ,SHOP_WDAYS##2 ,SHOP_WDAYS##3,SHOP_WDAYS##4 ,SHOP_WDAYS##5 ,SHOP_WDAYS##6 ,SHOP_WDAYS##7;
SQL SELECT UPPER(SHOP_SITE) SHOP_SITE,SHOP_WDAYS##1 ,SHOP_WDAYS##2 ,SHOP_WDAYS##3,SHOP_WDAYS##4 ,SHOP_WDAYS##5 ,SHOP_WDAYS##6 ,SHOP_WDAYS##7
FROM SHOP_CAL
WHERE SHOP_WKCTR=' '
AND SHOP_MCH=' ' ;

table1:
load
WO_NBR,
WO_LOT,
WO_REL_DATE,
PT_MFG_LEAD,
WO_SITE,
WO_STATUS,
RCTDATE;
SELECT WO_NBR,WO_LOT,WO_REL_DATE,WO_DUE_DATE,PT_MFG_LEAD,WO_SITE,WO_STATUS,RCTDATE FROM wo_mstr
FOR I= 0 TO $(TOT_RECORD)-1
LET WO_NBR= PEEK('WO_NBR',$(I),'table1');
LET WO_LOT= PEEK('WO_LOT',$(I),'table1');
LET date_offset = PEEK('PT_MFG_LEAD',$(I),'table1');
LET site = PEEK('WO_SITE',$(I),'table1');
LET start_date =  Date(PEEK('WO_REL_DATE',$(I),'table1'),'MM/DD/YYYY');
LET WO_STATUS = PEEK('WO_STATUS',$(I),'table1');
LET RCTDATE =  Date(PEEK('RCTDATE',$(I),'table1'),'MM/DD/YYYY');
let L_INTERVAL=0;
let L_FORWARD =0;
let L_FIND_DATE =0;
let curr_day =0;
let HLDY_COUNT =0;
let L_HOLIDAY=0 ;
LET DAY1=-1;
LET DAY2=-1;
LET DAY3=-1;
LET DAY4=-1;
LET DAY5=-1;
LET DAY6=-1;
LET DAY7=-1;
let curr_day = 'Mon';

if date_offset >= 0 then
  L_INTERVAL = $(date_offset);
  L_FORWARD = 1;
else
       L_INTERVAL = -1*$(date_offset);
       L_FORWARD = -1;
end if

CYCLEDATE_TABLE :
LOAD
  SHOP_WDAYS##1 ,SHOP_WDAYS##2 ,SHOP_WDAYS##3,SHOP_WDAYS##4 ,SHOP_WDAYS##5 ,SHOP_WDAYS##6 ,SHOP_WDAYS##7
  Resident [SHOP_CAL]
  where  [SHOP_SITE]='$(site)';

if PEEK('SHOP_WDAYS##1',0,'CYCLEDATE_TABLE') = 1 or PEEK('SHOP_WDAYS##1',0,'CYCLEDATE_TABLE') = 0 then
  LET DAY1 = PEEK('SHOP_WDAYS##1',0,'CYCLEDATE_TABLE');
  LET DAY2 = PEEK('SHOP_WDAYS##2',0,'CYCLEDATE_TABLE');
  LET DAY3 = PEEK('SHOP_WDAYS##3',0,'CYCLEDATE_TABLE');
  LET DAY4 = PEEK('SHOP_WDAYS##4',0,'CYCLEDATE_TABLE');
  LET DAY5 = PEEK('SHOP_WDAYS##5',0,'CYCLEDATE_TABLE');
  LET DAY6 = PEEK('SHOP_WDAYS##6',0,'CYCLEDATE_TABLE');
  LET DAY7 = PEEK('SHOP_WDAYS##7',0,'CYCLEDATE_TABLE');
  let L_FIND_DATE = Date(start_date ,'MM/DD/YYYY');
    do while L_INTERVAL > 0 and (curr_day = 'Mon' or  curr_day = 'Tue' or  curr_day = 'Wed' or  curr_day = 'Thu' or  curr_day = 'Fri' or  curr_day = 'Sat' or  curr_day = 'Sun')
   LET L_FIND_DATE = Date(L_FIND_DATE + L_FORWARD,'MM/DD/YYYY');
   curr_day=weekday(L_FIND_DATE);
   if curr_day = 'Mon' or  curr_day = 'Tue' or  curr_day = 'Wed' or  curr_day = 'Thu' or  curr_day = 'Fri' or  curr_day = 'Sat' or  curr_day = 'Sun' then
    temp_hd:
    LOAD
     1 as HLDY
     Resident [HOLIDAY]
     where  [HD_SITE]='$(site)' and date([HD_DATE],'MM/DD/YYYY')='$(L_FIND_DATE)';
     let HLDY_COUNT = peek('HLDY',0,'temp_hd');
     drop table temp_hd;
     if HLDY_COUNT <> 1 and curr_day = 'Sun' and DAY1 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Mon' and DAY2 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Tue' and DAY3 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Wed' and DAY4 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> '$(site)' and curr_day = 'Thu' and DAY5 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) -  1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Fri' and DAY6 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     elseif HLDY_COUNT <> 1 and curr_day = 'Sat' and DAY7 = 1 then
      LET L_INTERVAL = $(L_INTERVAL) - 1;
     END IF
    end if
    loop
END IF
DROP TABLE CYCLEDATE_TABLE;

I_FILE:

    load '$(WO_NBR)' WO_NBR,'$(WO_LOT)' WO_LOT,'$(L_FIND_DATE)' as CYCLEDATE

    autogenerate 1;

  NEXT
  DROP TABLE SHOP_CAL;
  drop table table1;
  DROP TABLE HOLIDAY;

-----------------------

I even tried this too

I_FILE:

    load '$(WO_NBR)' WO_NBR,'$(WO_LOT)' WO_LOT,'$(L_FIND_DATE)' as CYCLEDATE

    autogenerate $(TOT_RECORD);

But even this is giving the same error