Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Team,
i Stuck a Requirement.I have data where three field.Sub_CAT_CD and MC_CD AND QTY.
And One Sub_cat_Code Contain many MC_CD.
If i make two table First Sub_cat_cd wise QTY
Second is MC_CD WISE QTY
Simple Expression Sum(QTY)
But when i select any MC_CD Then First Table Should be should Sub_CAT_CD_WISE QTY WHERE Selection MC_CD_EXIST.
For this i am sharing data.kindly help me
Sorry, attached a wrong qvw earlier.
If i Select any MC_CD THEN IN Sub_cat_CD Table Show Sum
try this
Data:
LOAD MC_CD,
SUB_CAT_CD,
QTY
FROM
[Data.xlsx]
(ooxml, embedded labels, table is FINAL);
Left Join
LOAD SUB_CAT_CD,
sum(QTY) as QTYNEW
Resident Data
Group by SUB_CAT_CD;
Create straight table
Dimension:
MC_CD
Expression:
sum(QTYNEW)
If i Select any MC LIKE 111010001 Then It Should be Show Sum(QTY) 909.
But in SUB_CAT_WISE Straight itr Should be show 7300.
Sum(QTY) Is same Both of table
Dear Team,
I made two table.
MC_Wise Sum(QTY) Sub_Cat_Wise Sum(QTY)
111010001 909 1110100 7300
If i Select MC_CD THEN IT IS OK For MC_CODE_WISE_TABLE
But In Sub_CAT TABLE IT SHOW THE That Sub_CAT_CD_WISE SUM(QTY) Above i Gave Example
Data:
LOAD MC_CD,
SUB_CAT_CD,
QTY
FROM
[Data.xlsx]
(ooxml, embedded labels, table is FINAL);
Left Join
LOAD SUB_CAT_CD,
sum(QTY) as QTYNEW
Resident Data
Group by SUB_CAT_CD;
Create 1 straight table
Dimension:
MC_CD
Expression:
sum(QTY)
Straight table 2
Dimension:
SUB_CAT_CD
Expression:
sum(QTYNEW)
Hi Gourav,
what about:
MC_CD | sum(QTY) | SUB_CAT_CD | sum(QTY) |
---|---|---|---|
111010001 | 909 | 1110100 | 7300 |
111010002 | 910 | 1110100 | 7300 |
111010003 | 911 | 1110100 | 7300 |
The last column is this expression:
Aggr(NODISTINCT sum({$<SUB_CAT_CD = p({<MC_CD = {$(=concat(MC_CD,','))}>}),MC_CD>}QTY),SUB_CAT_CD)
Cheers
Andrew
Hi Gourav,
This selection of MC_CD shows the table's functionality better:
MC_CD | sum(QTY) | SUB_CAT_CD | sum(QTY) |
---|---|---|---|
111010110 | 919 | 1110101 | 4570 |
111010111 | 920 | 1110101 | 4570 |
111010201 | 132 | 1110102 | 3711 |
111010202 | 133 | 1110102 | 3711 |
I would suggest not to use the complex expression as you can easily group the data in script. Which will make the front expression easy
Data:
LOAD MC_CD,
SUB_CAT_CD,
QTY
FROM
[Data.xlsx]
(ooxml, embedded labels, table is FINAL);
Left Join
LOAD SUB_CAT_CD,
sum(QTY) as QTYNEW
Resident Data
Group by SUB_CAT_CD;
Create the straight table like below
Kindly Send me QVW