Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Urgent help: At Script level- Subtract or joints or mapping load

Hi,

   

i'm wondering if someone can help me, I'm trying to build weekly sales from MTD numbers.

 

basically every Monday we download data from data warehouse which is at MTD format, I want to build something that i can start extracting data at first  Monday of every month and save it as week 1 then following Monday download the data but subtract the previous week and then save it as new QVD file.

 

I tried left joints but failed so i though i can do it by mapping load but still can not get it work, can some one suggest something ?


he:
Load FISCAL_YEAR,[In CCC] from [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_6_30](QVD);

k:
Mapping load
FISCAL_YEAR,
[In CCC] as incccmd
Resident he;
DROP TABLE he;

h:
Load *from [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_6_31](QVD);

to:
NoConcatenate
Load
[In CCC MD] as bo,FISCAL_YEAR,

ApplyMap('k', FISCAL_YEAR,0)as incheck

Resident h;
Drop Table h;

7 Replies
vishsaggi
Champion III
Champion III

What you mean by subtract previous week? Can you elaborate a little bit here?

Anonymous
Not applicable
Author

Hi,

so lets say  we have QVW file to extract the SQL data at First monday of the month and we can generate below table and save it as QVD week1. its the MTD column that gets updated.

   

WK1
Year PeriodCompanyproduct Value MTD
20188AYY222
20188BJJ333
20188Ck44
20188At77
20188Bn-12
20188Cn-84.4
20188An

-156.8

following monday we use the same code and generate the below table and save it as QVD MTD

   

MTD
Year PeriodCompanyproduct value MTD
20188AYY444
20188BJJ555
20188Ck66
20188At44
20188Bn22
20188Cn34
20188A

n

55

i'm looking for away to be able to subtract the QVD MTD from QVD WK1 to get the wK2 result table below:

   

WK2
Year PeriodCompanyproduct Value MTD
20188AYY222
20188BJJ222
20188Ck22
20188At-33
20188Bn34
20188Cn118.4
20188An211.8
bertinabel
Creator
Creator

Hi, Darren.

    Well, if I undertand your problem is with the "Mapping"?

    If so, Could you try with next suggest?

to:
NoConcatenate

Load

    *

,ApplyMap('k', FISCAL_YEAR,0)as incheck

;
Load
[In CCC MD] as bo,FISCAL_YEAR,
Resident h;
Drop Table h;



In this case I´m using a preceded loading.


I hope I have been helpful.


Regards

Anonymous
Not applicable
Author

Hi, thanks, I tried this but still doesnt bring the value from first qvd, any suggestion for workaroud. ?

vishsaggi
Champion III
Champion III

Try this?

Change the path to qvd path after FROM clause.

WK1:

LOAD Year,

     Period,

     Company,

     product,

     [Value MTD]

FROM

[https://community.qlik.com/thread/310473]

(html, utf8, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

LEFT JOIN(WK1)

MTD:

LOAD Year,

     Period,

     Company,

     product,

     [value MTD] AS MTDValue

FROM

[https://community.qlik.com/thread/310473]

(html, utf8, embedded labels, table is @2, filters(

Remove(Row, Pos(Top, 1))

));

NoConcatenate

FinalMTD:

LOAD *, MTDValue-[Value MTD] AS MTDDiff

Resident WK1;

Drop Table WK1;

Store FinalMTD INTO filepath\FinalMTD.qvd(qvd);

Anonymous
Not applicable
Author

Many thanks Vishwarath, I really appreciate your help

so my final code looks like this now: but it is still failing to work. any chance you could advice if i have made a error?


WK1:

LOAD GL_ACCOUNT_CODE,LEGAL_ENTITY_CODE,COMPANY_CODE,PROFIT_CENTER_CODE,MRC,VERSION_CODE,FISCAL_YEAR,FISCAL_YEAR_PERIOD_NUMBER,Week,NTS_Factor,
[In CCC],TRANSACTIONAL_MTD_QTY,BRAVO_PRODUCT_CODE,BRAVO_PRODUCT_CODE as ORIG_BRAVO_PRODUCT_CODE,SourceType, VERSION_TYPE_CODE, %GEO_HIER_KEY,
%BRAVO_HIER_KEY,SRC_SYS_ID, [In CCC ORG]
FROM  [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8_1*]QVD (html, utf8, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1))));





LEFT JOIN(WK1)

MTD:

LOAD GL_ACCOUNT_CODE,LEGAL_ENTITY_CODE,COMPANY_CODE,PROFIT_CENTER_CODE,MRC,VERSION_CODE,FISCAL_YEAR,FISCAL_YEAR_PERIOD_NUMBER,Week,NTS_Factor,
[In CCC],TRANSACTIONAL_MTD_QTY,BRAVO_PRODUCT_CODE,BRAVO_PRODUCT_CODE as ORIG_BRAVO_PRODUCT_CODE,SourceType, VERSION_TYPE_CODE, %GEO_HIER_KEY,
%BRAVO_HIER_KEY,SRC_SYS_ID, [In CCC ORG]AS MTDValue
FROM
[$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8*]QVD (
html, utf8, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1))));



NoConcatenate
FinalMTD:
LOAD *, MTDValue-[In CCC] AS MTDDiff
Resident WK1;

Drop Table WK1;
Store FinalMTD INTO [$(vQVDPath)\FinalMTD.qvd](qvd);

vishsaggi
Champion III
Champion III

Can you check here

FROM  [$(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8_1*]QVD


May be this you are trying to load?


FROM  $(vQVDPath)\PRJ_FACT_SALES_COGS_BAL_WK_A_2018_8_1*.QVD(qvd)