Results 1 to 2 of 2

Thread: Query is reading the same table twice

  1. #1
    Neophyte parkourtofu's Avatar
    Join Date
    Sep 2020
    Excel Version
    Excel Office 365

    Question Query is reading the same table twice

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear experts,

    i would like to seek help in improving the efficiency of my codings below.

    my want is this, a consolidated summary in powerpivot that shows number of policies and sum of premiums according to their "FILE" and "ZMOVECODE":
    Book5.xlsx (28.2 KB)

    GOAL: i have a total of 100GB csv documents as raw datasets and i would like to produce a pivot table like above

    at my coding:
    i have stage query to import the respective files
    • SQ_ActVal_IFBEG
    • SQ_ActVal_ET
    • SQ_ActVal_EX
    • SQ_ActVal_IF

    TRAD_LForm_Table <- it's a table i extract a few infos (i.e. LFormDesc,Productname and Fund4) to form the pivot table above
    fnGetTotPrem<- it's a function i sumifs the premiums by "FILE"(which file were the policy number from), "CHDRNUM"(policy number) and "RECORD"(count)

        sourceIFBEG = SQ_ActVal_IFBEG,
        #"Merged Queries1" = Table.NestedJoin(sourceIFBEG, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
        #"Expanded TRAD_LForm_Table1" = Table.ExpandTableColumn(#"Merged Queries1", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
        IFBEG = Table.SelectRows(#"Expanded TRAD_LForm_Table1", each ([LFormDesc] <> null)),
        IFBEG_Prem = fnGetTotPrem(IFBEG),
        sourceET = SQ_ActVal_ET,
        #"Merged Queries2" = Table.NestedJoin(sourceET, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
        #"Expanded TRAD_LForm_Table2" = Table.ExpandTableColumn(#"Merged Queries2", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
        ET = Table.SelectRows(#"Expanded TRAD_LForm_Table2", each ([LFormDesc] <> null)),
        ET_Prem = fnGetTotPrem(ET),
        sourceEX = SQ_ActVal_EX,
        #"Merged Queries3" = Table.NestedJoin(sourceEX, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
        #"Expanded TRAD_LForm_Table3" = Table.ExpandTableColumn(#"Merged Queries3", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
        EX = Table.SelectRows(#"Expanded TRAD_LForm_Table3", each ([LFormDesc] <> null)),
        EX_Prem = fnGetTotPrem(EX),
        sourceIF = SQ_ActVal_IF,
        #"Merged Queries4" = Table.NestedJoin(sourceIF, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
        #"Expanded TRAD_LForm_Table4" = Table.ExpandTableColumn(#"Merged Queries4", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
        IF = Table.SelectRows(#"Expanded TRAD_LForm_Table4", each ([LFormDesc] <> null)),
        IF_Prem = fnGetTotPrem(IF),
        L678_NOP = Table.Combine({IFBEG,ET,EX,IF}),
        L678_PREM = Table.Combine({IFBEG_Prem,ET_Prem,EX_Prem,IF_Prem}),
        L678 = Table.NestedJoin(L678_NOP,{"FILE","CHDRNUM","RECORD"},L678_PREM,{"FILE","CHDRNUM","RECORD"},"new",JoinKind.LeftOuter),
        #"Expanded new" = Table.ExpandTableColumn(L678, "new", {"Total_Prem"}, {"Total_Prem"})
        #"Expanded new"
    then here i have. a data ready for powerpivot to do what i wanted. here's the issue that it takes too long to load, and the size im working with now is a fraction of the 100GB that im intended to do for.

    please may i seek the expert's view on how can i improve this coding so that it wont read the same table twice so it reduces the time it takes to load at this stage.

    Thank you very much,
    Attached Files Attached Files
    Last edited by Bob Phillips; 2020-11-08 at 06:57 PM. Reason: Added code tags

  2. #2
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Excel Version
    Office 365
    You did not explain how the 100GB csv documents were composed. If this is, for example, one for each contract, then you could create a pivoted table with the summary values after the first import and remember by means of a query up to which date you have imported the csv documents.

    The next time you use this date as a filter to import only newer documents and consolidate them with the last import.

    But since you didn't explain how the csv-documents result, I have no idea if my suggestion can be useful.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts