Update New Folder Data

[Ramu]

Hi All ,

Please Help me,

Scenario 1:

I have a Qliksense application. In that Monthly Data is coming , Means

For Ex:- This month is Jan. data folder path have name JAN Folder, Now JAN Folder data (having 5 Excel sheets) is reflecting in App, 

FEB month one more folder is added, In that Data Source having JAN and FEB Folders , Now I want to reflect the Jan, and Feb folders data in dashboard,

MAR Month one more folder is added to Data source , Mar month App displaying , Jan, Feb, Mar Data,

Like Every month New folder is added, Every folder have same Excel sheets and data is different.

I want to add the all folders  data into dashboard, 

dynamically add the newly added folder data into app.

Scenario 2:

I have data like 

In Put File:

F_Name   active   F_flage    C_Name1    C_Name2    C_Name3     C_Name4      C_Role1    C_Role2    C_Role3    C_Role4 

Finance   A                1                 Rock                Joy                Bil                   James           MGR           Sr.M             typist            cleark

C_Loc1    C_Loc2     C_Loc3    C_Loc4

HYD            PUN              CHI           MUM

Required Out Put is:

Out Put Is:

F_Name          active      F_flage     C_Name       Role          Loc

Finance              A                 1                 Rock           MGR           HYD

Finance              A                 1                 Joy              Sr.M          PUN

Finance              A                 1                 Bil                typist         CHI

Finance              A                 1                 James        cleark          MUM

Please  Help me ,

Thanks,

[raji6763]

hi Ramu,

here is the solution  for your scenario

scenario:2

you can use concatenate statement to achieve the above scenario

Script:

combined:
LOAD
"F_Name ",
" active ",
" F_flage ",
"C_Name1 " as C_Name,
"  C_Role1 " as C_Role,
"C_Loc1 " as C_Loc

FROM [lib://DataFiles/multiple columns in to one column.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(combined)
T2:
LOAD
"F_Name ",
" active ",
" F_flage ",
"C_Name2 " as C_Name,
"C_Role2 " as C_Role,
"C_Loc2 " as C_Loc

FROM [lib://DataFiles/multiple columns in to one column.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(combined)
t3:
LOAD
"F_Name ",
" active ",
" F_flage ",
"C_Name3 " as C_Name,
"C_Role3 " as C_Role,
" C_Loc3 " as C_Loc

FROM [lib://DataFiles/multiple columns in to one column.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(combined)
t4:
LOAD
"F_Name ",
" active ",
" F_flage ",
" C_Name4 " as C_Name,
"C_Role4 " as C_Role,
C_Loc4 as C_Loc

FROM [lib://DataFiles/multiple columns in to one column.xlsx]
(ooxml, embedded labels, table is Sheet1);

output:

scenario:1

you may be use filetime()  function

regards,

raji

[Ramu]

Hi Raji,

Thanks for Your valuable solution.

Here one more problem is there, Column names are not fixed  Every month Data is chaining means , C_Names,C_Roles,C_Loc  Each one having 10 or 7 or  N times.

like that means , C_Name1 , C_name2 ....C_nameN , Roles  and Loc also, in this situation , we will use 'n' times Concatenate or any alternative solution ?

scenario:1

you may be use filetime()  function --- Is not working.

Regards,

Ram 

[raji6763 · Accepted solution]

Scenario1:

Another way is use Crosstable() function

scenario 2:  you can check this link

https://community.qlik.com/t5/QlikView-App-Development/Load-the-latest-file-of-a-folder/td-p/192461

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD * INLINE [
    F_Name, active, F_flage, C_Name1, C_Name2, C_Name3, C_Name4, C_Role1, C_Role2, C_Role3, C_Role4, C_Loc1, C_Loc2, C_Loc3, C_Loc4 
    Finance, A, 1, Rock, Joy, Bil, James, MGR, Sr.M, typist, cleark, HYD, PUN, CHI, MUM
];

tab1X:
CrossTable(Hdr, C_Name, 3)
LOAD F_Name, active, F_flage, C_Name1, C_Name2, C_Name3, C_Name4
Resident tab1;

tab2X:
CrossTable(Hdr2, C_Role, 3)
LOAD F_Name, active, F_flage, C_Role1, C_Role2, C_Role3, C_Role4
Resident tab1;

tab3X:
CrossTable(Hdr3, C_Loc, 3)
LOAD F_Name, active, F_flage, C_Loc1, C_Loc2, C_Loc3, C_Loc4
Resident tab1;

tab2:
LOAD *, AutoNumber(Hdr,'Name') As Key
Resident tab1X;
Left Join(tab2)
LOAD *, AutoNumber(Hdr2,'Role') As Key
Resident tab2X;
Left Join(tab2)
LOAD *, AutoNumber(Hdr3,'Loc') As Key
Resident tab3X;

Drop Table tab1, tab1X, tab2X, tab3X;

[Saravanan_Desingh]

Output:

[Ramu]

Hi Saran,

Thanks for your valuable solution.

Regards,

Ram