What is a queue? In programming terms it is very similar to every day life terms. When you stand in line waiting to order a coffee at the cafe, you are standing in a queue. It’s a systematic way of delivering in an order. The person that got in line in front of you, get’s to place their order first. It’s something that has become the norm so that chaos doesn’t ensue.

From a reporting standpoint you usually don’t need to worry about this. The data is already there, the timing or order of pulling it and analyzing it isn’t that crucial, as long as it gets done. This begins to change as you are working with real-time streaming or working with concurrency, but as a generalization it is rare. So for those rare instances, here is a way to handle it.

This particular example covers extracting monthly data from a database that isn’t stable. What I am doing is creating a queue of periods to query, and trying until I succeed.

Steps:

1. Determine the list of periods I need to query.
2. Check QVD files and build a list of those not queried yet this reload.
3. Query the next period in line.
4. Repeat steps 2 and 3 until complete.


//    **********************************************
//    **
//    **    Extract Queue
//    **
//    **********************************************

//    **********************************************
//    **    Config 
//    **********************************************

Let vReloadTime = Num(Now());
Set ErrorMode = 0;


//    **********************************************
//    **    Sub 
//    **********************************************

Sub FileCheck

    ExtractPeriod:
    Load
        Null() as EXTRACTED_PERIOD
    AutoGenerate    
        (0);

    Let vDate = Date(Today(),'YYYYMMDD');


    For each file in filelist('Lib://QVD/V_FACT_GL_*.qvd');

        Let vP = Replace(Upper(Subfield('$(file)','_',-1)),'.QVD','');

        Let vFileTime = Num(FileTime('$(file)'));

        If vFileTime > vReloadTime Then

            Concatenate(ExtractPeriod)
            Load Distinct 
                $(vP) as EXTRACTED_PERIOD
            AutoGenerate
                (1);

        End If;

    Next file;

End Sub;


//    **********************************************
//    **    Set Up 
//    **********************************************

Periods:
Load Distinct
    Year(Date_tmp)&Num(Month(Date_tmp),'00') as PERIOD;
Load 
    MakeDate(2017)+(iterNo()-1) as Date_tmp
AutoGenerate
    (1)
While 
    MakeDate(2017)+(iterNo()-1) <= Today();

Call FileCheck;

Queue:
Load
    PERIOD  as NEXT_PERIOD
Resident    
    PERIOD 
Where
    Not Exists(EXTRACTED_PERIOD, PERIOD);


//    **********************************************
//    **    Execute 
//    **********************************************

Do While NoOfRows('Queue') > 0

    Let vPERIOD = Peek('NEXT_PERIOD',$(p),'Queue');
    
    Trace --------------------------------- Querying Period: $(vPERIOD);
    
    LIB CONNECT TO 'DATABASE';
    
    V_FACT_GL:
    SQL
    SELECT * FROM "DB"."V_FACT_GL" WHERE PERIOD = '$(vPERIOD)';
    
    Disconnect; 
    
    Store V_FACT_GL into [Lib://QVD/V_FACT_GL_$(vPERIOD).qvd](qvd);
    
    Drop Table V_FACT_GL;
    
    
    Drop Tables ExtractPeriod, Queue;
    
    Call FileCheck;
    
    Queue:
    Load
        PERIOD  as NEXT_PERIOD
    Resident    
        PERIOD 
    Where
        Not Exists(PERIOD, EXTRACTED_PERIOD);
   
Loop


exit script;