Solved: split common table in two table - Qlik Community

theboss-123 · ‎2021-01-07

hello in database of ERP we have a table contain data of two column in other table

i want in qlikview to view for each column their in other column please see the enclosed file there is an example

i want to keep reception in column and make reception supplier and plan arrangement in other column

theboss-123 · ‎2021-01-10

THIS SCRIPT WORKED FINE

PIECE:
load
VCRNUM_0 as Piece,
VCRNUMORI_0 as Piece_Original,
IPTDAT_0,
QTYPCU_0 as Qte,
VCRTYP_0 as PIECE_TYPE,
TRSTYP_0 as Id_Dim1,
LANNUM_0 as LANNUM1,
LANMES_0 as pieces,
LOC_0,
BPRNUM_0 as FRS,
ITMREF_0 As Ref,
ITMDES1_0 as Designation,
STOFCY_0 as Site,

Date(IPTDAT_0) As Date,
        Year(IPTDAT_0) As Year,
        Month(IPTDAT_0) As Month,
        Day(IPTDAT_0) As Day,
        Week(IPTDAT_0) As Week,
        'Q' & Ceil(Month(IPTDAT_0)/3) As Quarter,
        WeekYear(IPTDAT_0) & Num(Week(IPTDAT_0), '00') As YearWeek,
        Year(IPTDAT_0) & '-Q' & Ceil(Month(IPTDAT_0)/3) As YearQuarter,
        Date(MonthStart(IPTDAT_0), 'YYYY-MM') As YearMonth,
        -Year2Date(IPTDAT_0) As YTD_Flag,
        -Year2Date(IPTDAT_0, -1) As LYTD_Flag;

SQL SELECT *
FROM sagex3lab.dbo."JOURNAL STOCK MENU";
//-------- End Multiple Select Statements ------

Transaction:
load
TRSTYP_0 as Id_Dim1,
LANNUM_0 as LANNUM2,
LANMES_0 as transaction;
SQL SELECT *
FROM sagex3lab.dbo."JOURNAL STOCK MENU LOCAL V2";

View solution in original post

Anil_Babu_Samineni · ‎2021-01-14

Please accept solution If that is resolved, so that it will be easy for next landers.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

edwin · ‎2021-01-07

from your example, reception, supplier, and plan arrangement are entries/values in A column these are not fields. when you say column did you mean a list or columns in a table? if these become columns, what values would be under them? do you have a QVW sample?

theboss-123 · ‎2021-01-07

is there a logical way to split the clumn where it is the entries

reception, supplier, and plan arrangement

for example keep reception in column A and put other in column B

edwin · ‎2021-01-07

it sound like you want to do the opposite of a cross tab, look at this if this will help:

Solved: Opposite of a cross table - Qlik Community - 5731

theboss-123 · ‎2021-01-07

in my case i called a view from SQL server,can you shw me how to establish the opposite of crosstable

edwin · ‎2021-01-07

Solved: Opposite of a cross table - Qlik Community - 5731

Anil_Babu_Samineni · ‎2021-01-09

What is there in the excel is the output you want? If so, what is the source. Please post sample input from source and the expected result.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

QFabian · ‎2021-01-09

HI @theboss-123 , please check if this kind of script helps :

Receptions:
Load
TypePiece as Id_Dim1
LANMES_0 as Reception
From your source
Where TransactionType = 3;

PlanRangement
Load
TypePiece as Id_Dim1
LANMES_0 as PlanRangement
From your source
Where TransactionType = 35 or TransactionType = 22;

QFabian

theboss-123 · ‎2021-01-10

Hi Fabian thanks for your suggestion

but kindly informed that transaction type and type piece are both in relationship with LANMES_0

theboss-123 · ‎2021-01-10

this is my script

PIECE:
load
VCRNUM_0 as Piece,
VCRNUMORI_0 as Piece_Original,
IPTDAT_0,
QTYPCU_0 as Qte,
VCRTYP_0 as Id_Dim1,
LANNUM_0 as LANNUM,
LANMES_0 as pieces,
LOC_0,
BPRNUM_0 as FRS,
ITMREF_0 As Ref,
ITMDES1_0 as Designation,
STOFCY_0 as Site.

SQL SELECT *
FROM sagex3lab.dbo."JOURNAL STOCK MENU";
//-------- End Multiple Select Statements ------

Transaction:
load
TRSTYP_0 as Id_Dim1,
LANNUM_0 as LANNUM2,
LANMES_0 as transaction;
SQL SELECT *
FROM sagex3lab.dbo."JOURNAL STOCK MENU LOCAL V2";

any suggesion ,