29 Replies Latest reply: Dec 22, 2012 6:30 AM by Gaurav Malhotra RSS

    Urgent Help

    Gaurav Malhotra

      Hi there,

       

      I am having 3 excel files

       

      having fields:

       

      ProductName, Quantity, OpeningStock

      ProductName, Quantity, PrimarySales

      ProductName, Quantity, SecondarySales

      resp.

       

      I have to load these 3 files in qlikview & then calculate a new field called "RemainingStock",

      which is a calculated field.

       

      RemainingStock = (OpeningStock + PrimarySales) - SecondarySales

       

      How can it be done.

       

      Regards,

      Gaurav Malhotra

        • Re: Urgent Help

          Hi Gaurav,

           

          1. You just need to load 3 excel files

           

           

          2. Create a straight table with remaining stock dimension.

           

          View attached example.

           

          Hope it helps

           

          Thanks,

          Supriya

          • Re: Urgent Help
            Neha Rangari

            Hi Gaurav,

             

            Try following code:

             

            temp:

            LOAD ProductName,

                      Quantity,

                      OpeningStock

            FROM

            C:\Users\Desktop\Book1.xlsx

            (ooxml, embedded labels);

             

            INNER JOIN

            LOAD ProductName,

                      Quantity,

                      PrimarySales

            FROM

            C:\Users\Desktop\Book2.xlsx

            (ooxml, embedded labels);

             

            INNER JOIN

            LOAD ProductName,

                      Quantity,

                      SecondorySales

            FROM

            C:\Users\Desktop\Book3.xlsx

            (ooxml, embedded labels);

             

            data:

            LOAD *,

                     (OpeningStock + PrimarySales) - SecondarySales as RemainingStock

            RESIDENT temp;

             

            DROP TABLE temp;

             

            Hope this helps.

             

            BR, Neha

              • Re: Urgent Help
                Gaurav Malhotra

                Thanks Supriya & Neharangari. Sorry, I never mentioned that all 3 tables are crosstables. I guess 'Join' don't work in crosstables.

                  • Re: Urgent Help
                    - -

                    hi gaurav

                     

                                     Can u atlst provide screen shots ?

                      • Re: Urgent Help
                        Gaurav Malhotra

                        Screenshot of what ???

                         

                        1. Application developed

                        2. Excel files

                        3. Code I wrote.

                          • Re: Urgent Help
                            - -

                            since u have personal edition

                            provide screen shots for code u have or jst attach the excel file from whr u r getting data for all...........

                              • Re: Urgent Help
                                Jagan Nalla

                                Try this,

                                Alias F1 as S.no., F2 as Product, F3 as Quantity;

                                 

                                FiscalCalendar:

                                LOAD * Inline

                                [Month, Quarter

                                Jan, Q4

                                Feb, Q4

                                Mar, Q4

                                Apr, Q1

                                May, Q1

                                Jun, Q1

                                Jul, Q2

                                Aug, Q2

                                Sep, Q2

                                Oct, Q3

                                Nov, Q3

                                Dec, Q3];

                                 

                                Main:

                                LOAD Month,OpeningStock;

                                CrossTable(Month,OpeningStock,1)

                                LOAD

                                F1,

                                Apr,

                                May,

                                Jun,

                                Jul,

                                Aug,

                                Sep,

                                Oct,

                                Nov,

                                Dec,

                                Jan,

                                Feb,

                                Mar

                                FROM

                                [C:\Users\test\Downloads\Rajasthan Opening Stock.xlsx]

                                (ooxml, embedded labels, header is 3 lines, table is RJ, filters(

                                Remove(Row, Pos(Top, 33)),

                                Remove(Row, Pos(Top, 32)),

                                Remove(Row, Pos(Top, 28)),

                                Remove(Row, Pos(Top, 24)),

                                Remove(Row, Pos(Top, 20)),

                                Remove(Row, Pos(Top, 16)),

                                Remove(Row, Pos(Top, 11)),

                                Remove(Row, Pos(Top, 6)),

                                Remove(Col, Pos(Top, 20)),

                                Remove(Col, Pos(Top, 19)),

                                Remove(Col, Pos(Top, 18)),

                                Remove(Col, Pos(Top, 17)),

                                Remove(Col, Pos(Top, 1))

                                ));

                                 

                                 

                                Left Join

                                LOAD Month,PrimarySales;

                                CrossTable(Month,PrimarySales,1)

                                LOAD

                                F2,

                                Apr,

                                May,

                                Jun,

                                Jul,

                                Aug,

                                Sep,

                                Oct,

                                Nov,

                                Dec,

                                Jan,

                                Feb,

                                Mar

                                FROM

                                [C:\Users\test\Downloads\Rajasthan Primary Sale.xlsx]

                                (ooxml, embedded labels, header is 3 lines, table is RJ, filters(

                                Remove(Row, Pos(Top, 33)),

                                Remove(Row, Pos(Top, 32)),

                                Remove(Row, Pos(Top, 28)),

                                Remove(Row, Pos(Top, 24)),

                                Remove(Row, Pos(Top, 20)),

                                Remove(Row, Pos(Top, 16)),

                                Remove(Row, Pos(Top, 11)),

                                Remove(Row, Pos(Top, 6)),

                                Remove(Col, Pos(Top, 20)),

                                Remove(Col, Pos(Top, 19)),

                                Remove(Col, Pos(Top, 18)),

                                Remove(Col, Pos(Top, 17)),

                                Remove(Col, Pos(Top, 1))

                                ));

                                 

                                 

                                Left Join

                                LOAD Month,SecondarySales;

                                CrossTable(Month,SecondarySales,1)

                                LOAD

                                F3,

                                Apr,

                                May,

                                Jun,

                                Jul,

                                Aug,

                                Sep,

                                Oct,

                                Nov,

                                Dec,

                                Jan,

                                Feb,

                                Mar

                                FROM

                                [C:\Users\test\Downloads\Secondary rajasthan(2009-10).xlsx]

                                (ooxml, embedded labels, header is 3 lines, table is [RJ (Secondary)], filters(

                                Remove(Row, Pos(Top, 33)),

                                Remove(Row, Pos(Top, 32)),

                                Remove(Row, Pos(Top, 28)),

                                Remove(Row, Pos(Top, 24)),

                                Remove(Row, Pos(Top, 20)),

                                Remove(Row, Pos(Top, 16)),

                                Remove(Row, Pos(Top, 11)),

                                Remove(Row, Pos(Top, 6)),

                                Remove(Col, Pos(Top, 21)),

                                Remove(Col, Pos(Top, 20)),

                                Remove(Col, Pos(Top, 19)),

                                Remove(Col, Pos(Top, 18)),

                                Remove(Col, Pos(Top, 17)),

                                Remove(Col, Pos(Top, 1))

                                ));

                                 

                                Res_Temp:

                                LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock  Resident Temp;

                                  • Re: Urgent Help
                                    - -

                                    hi gaurav,

                                            the code provided by jagan is correct........jst dont include load statement....

                                    look below given code:

                                     

                                     

                                     

                                    CrossTable

                                    (Month, Opening)

                                    LOAD F1,

                                        
                                    Apr,

                                        
                                    May,

                                        
                                    Jun,

                                        
                                    Jul,

                                        
                                    Aug,

                                        
                                    Sep,

                                        
                                    Oct,

                                        
                                    Nov,

                                        
                                    Dec,

                                        
                                    Jan,

                                        
                                    Feb,

                                        
                                    Mar

                                    FROM

                                    [D:\QlikView\APPLICATIONS\RND\Rajasthan Opening Stock.xlsx]

                                    (
                                    ooxml, embedded labels, header is 3 lines, table is RJ, filters(

                                    Remove(Col, Pos(Top, 1)),

                                    Remove(Col, Pos(Top, 1)),

                                    Remove(Col, Pos(Top, 1)),

                                    Remove(Row, Pos(Top, 6)),

                                    Remove(Row, Pos(Top, 10)),

                                    Remove(Row, Pos(Top, 14)),

                                    Remove(Row, Pos(Top, 17)),

                                    Remove(Row, Pos(Top, 20)),

                                    Remove(Row, Pos(Top, 23)),

                                    Remove(Row, Pos(Top, 26)),

                                    Remove(Row, Pos(Top, 26)),

                                    Remove(Col, Pos(Top, 15)),

                                    Remove(Col, Pos(Top, 14)),

                                    Remove(Col, Pos(Top, 15)),

                                    Remove(Col, Pos(Top, 14))

                                    ));


                                    i hope u can undrstand how to load excel wit help of cross table as given above.

                                     

                                      • Re: Urgent Help
                                        Gaurav Malhotra

                                        Capture1.JPG

                                          • Re: Urgent Help
                                            - -

                                            hi gaurav

                                                             Dont apply any joins..........jst load the cross tables tatz it..........

                                             

                                             

                                            regards,

                                            vaibhav.

                                              • Re: Urgent Help
                                                Gaurav Malhotra

                                                I also renamed "Main" (table name) in the code to "Temp".

                                                 

                                                but It doesn't work & show error like below:

                                                 

                                                Thanks & Regards

                                                  • Re: Urgent Help
                                                    Jagan Nalla

                                                    Gaurav,

                                                     

                                                    I want to expalin you few points.

                                                     

                                                    1. We are trying to develop a expression in script i.e. (OpeningStock + PrimarySales) - SecondarySales . Here we are calculating the expression on fields which are not there in in single table. Each field is coming from different tables. So we need to make single table to calculate this expression.

                                                     

                                                    2. To make a single table we need to use Joins concept. If we don't use joins the qvw loads the three different tables seperately and links the tables with help of common field key.

                                                     

                                                    So what i did here is first i'll load cross table of OpeningStock and in resident table(i.e. Main table) i'll hold the month and values of opening stock. Now i'll load crosstable of PrimarySales and i'll join the resident table of primarysales with Main table. Say thing for SecondarySales table.

                                                     

                                                    I forgot to tell you i'm also droping the tables of OpeningStock, PrimarySales, SecondarySales. B'coz we have this fields in main table. I tried with sample inline data please check for your reference.

                                                     

                                                    Alias F1 as S.no., F2 as Product, F3 as Quantity;

                                                     

                                                    FiscalCalendar:

                                                    LOAD * Inline

                                                    [Month, Quarter

                                                    Jan, Q4

                                                    Feb, Q4

                                                    Mar, Q4

                                                    Apr, Q1

                                                    May, Q1

                                                    Jun, Q1

                                                    Jul, Q2

                                                    Aug, Q2

                                                    Sep, Q2

                                                    Oct, Q3

                                                    Nov, Q3

                                                    Dec, Q3];

                                                     

                                                    OpeningStock:

                                                    CrossTable(Month,OpeningStock,1)

                                                    LOAD * Inline

                                                    [F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

                                                    100,200,300,400,500,600,100,200,300,400,500,600,1000];

                                                     

                                                    Main:

                                                    LOAD Month,OpeningStock Resident OpeningStock;

                                                    DROP Table OpeningStock;

                                                     

                                                    PrimarySales:

                                                    CrossTable(Month,PrimarySales,1)

                                                    LOAD * Inline

                                                    [F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

                                                    100,200,300,400,500,600,100,200,300,400,500,600,1000];

                                                     

                                                    Left Join (Main)

                                                    LOAD Month,PrimarySales Resident PrimarySales;

                                                    DROP Table PrimarySales;

                                                     

                                                    SecondarySales:

                                                    CrossTable(Month,SecondarySales,1)

                                                    LOAD * Inline

                                                    [F1,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar

                                                    10,20,30,40,50,60,10,20,30,40,50,60,10];

                                                     

                                                    Left Join (Main)

                                                    LOAD Month,SecondarySales Resident SecondarySales;

                                                    DROP Table SecondarySales;

                                                     

                                                    Resident_Main:

                                                    LOAD Month,(OpeningStock + PrimarySales) - SecondarySales as RemainingStock  Resident Main;

                                                    Cheers!

                                                    Hope it helps you.